Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [spatial]

Storing, querying, and indexing spatial data (including geometric and geographic representations).

RDBMS-specific Links

263 questions
23
votes
1 answer

Are unfixable spatial index corruptions considered normal?

I have a spatial index for which DBCC CHECKDB reports corruptions: DBCC CHECKDB(MyDB) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000'…
boot4life
  • 1,239
  • 1
  • 9
  • 19
20
votes
3 answers

What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?

I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table. However, I found out that POINT(X,Y) also works.…
ComSubVie
  • 303
  • 1
  • 2
  • 6
20
votes
3 answers

Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance

I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/advice about suitable DBMSs, data structure, or…
Azwok
  • 365
  • 1
  • 9
19
votes
3 answers

PostGIS vs. SQL Server for GIS data

So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle…
LowlyDBA - John M
  • 10,812
  • 11
  • 39
  • 59
17
votes
3 answers

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons. That being said, our database has…
Ryan Charmley
  • 271
  • 1
  • 2
  • 5
13
votes
2 answers

Alternative to MakeValid() for spatial data in SQL Server 2016

I have a very large table of geography LINESTRING data that I'm moving from Oracle to SQL Server. There are a number of evaluations that are executed against this data in Oracle, and they will need to be executed against the data in SQL Server,…
CaptainSlock
  • 454
  • 1
  • 6
  • 13
12
votes
3 answers

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),…
seb49
  • 121
  • 1
  • 3
12
votes
3 answers

Does MySQL store point datatypes as LAT LNG or LNG LAT?

I'm used to seeing location format as latitude followed by longitude, but using libraries, I believe I understand MySQL to store it as POINT(LNG LAT), the reverse order. Is my library in the wrong, or is this the actual format? I can't seem to find…
Lazlo
  • 223
  • 1
  • 2
  • 5
12
votes
1 answer

Decimal or Point Data Type for storing Geo location data in MySQL

I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options: 1) 2 columns for Each Address: Latitude DECIMAL(10, 8), Longitude DECIMAL(11, 8) 2) One Column for Each Address: GeoLocation…
Prabhat
  • 307
  • 2
  • 4
  • 15
12
votes
1 answer

How to optimize a query so that it seeks on one index first, and then another index after that

I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two sets such that their times match to a threshold…
user261963
  • 123
  • 5
9
votes
2 answers

How can I store latitude and longitude without PostGIS?

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a map. I am using HTML 5 mapping plus Google maps. I…
lz7cjc
  • 91
  • 1
  • 1
  • 2
9
votes
3 answers

How does Yelp efficiently calculate distance in the database?

For example, say I have a table: Business(BusinessID, Lattitude, Longitude) All are indexed of course. Also there are 1 million records Say I want to find businesses closest to 106,5, for example, how would I do so? If I do SELECT * FROM…
user4951
  • 1,325
  • 5
  • 20
  • 37
9
votes
4 answers

Problem getting zips in radius via MySQL

I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point. It just occurred to me that, just because a zip's center point is not within a…
I wrestled a bear once.
  • 115
  • 1
  • 9
8
votes
4 answers

MySQL Geo Spatial Query is very slow although index is used

I need to fetch records from a InnoDb Table by distance (must not be exactly) and sort by distance. The table has 10 million records. My best time is so far 8 sec (3 sec without order by distance), which make this not usable. How I could improve…
nenad007
  • 133
  • 1
  • 8
8
votes
3 answers

Cross join on a numbers table to get line vertices, is there a better way?

The Question: I have a spatial table (road lines), stored using ESRI's SDE.ST_GEOMETRY user-defined datatype in an Oracle 12c geodatabase. I want to list the line vertices so that I can ultimately access & update their coordinates. If I was using…
User1974
  • 1,323
  • 16
  • 42
1
2 3
…
17 18