Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
23
votes
1answer
884 views

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 ...
20
votes
3answers
658 views

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/...
17
votes
3answers
29k views

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. ...
15
votes
3answers
8k views

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 ...
14
votes
2answers
10k views

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 ...
13
votes
2answers
2k views

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 ...
12
votes
1answer
258 views

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 ...
11
votes
3answers
5k views

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 = ...
9
votes
4answers
574 views

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, ...
9
votes
3answers
3k views

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, ...
9
votes
2answers
4k views

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 ...
8
votes
3answers
572 views

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 ...
8
votes
1answer
17k views

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)...
7
votes
3answers
1k views

What are object-relational databases, and why is this model necessary in spatial databases?

Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational. What are object-relational databases, and why is it necessary to use this model in spatial ...
7
votes
4answers
1k views

How to optimize STDistance execution?

I am creating temporary table during stored procedure execution with the following structure: [ID] BIGINT [Point] GEOGRAPHY the ID is not unique - there are about 200 records for each ID. I need ...
7
votes
1answer
136 views

very poor cardinality estimate on a geometry intersection

I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results ...
7
votes
1answer
12k views

Mongodb - Returning the points nearest to a location, with distance

What is the MongoDB's equivalent to this query: SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2) + pow(106.772835-`Longitude`, 2) ...
7
votes
1answer
2k views

Some SQL Server geospatial queries take much, much longer than others

We have a simple SQL Server table with geospatial data that looks like this: CREATE TABLE [dbo].[Factors]( [Id] [int] IDENTITY(1,1) NOT NULL, [StateCode] [nvarchar](2) NOT NULL, [...
7
votes
2answers
128 views

How to setup solution for multi-dimensional distance search with SQL Server spatial data types

I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows. ...
7
votes
4answers
1k views

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), ...
7
votes
1answer
694 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
7
votes
1answer
550 views

Two servers, different execution plans. Several orders of magnitude difference in run time

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my ...
7
votes
1answer
598 views

ST_GeomFromGeoJSON() Causes postgres to crash

I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries. After some debugging, using the examples provided by postgis, it appears the ...
6
votes
1answer
310 views

SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?

PostGIS provides a function called ST_MakeLine(pt1,pt2). It's great for constructing lines. MySQL does the same with LineString(pt1,pt2). What do you call the SQL Server analog that takes two Points ...
6
votes
2answers
605 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM `...
6
votes
1answer
82 views

How do I force SQL Server to use my spatial index through a view?

I have tables which holds transactions for properties that are stored as lat long pairs. (There are more columns and datapoints then in my example schema). A common request is to find the ...
6
votes
1answer
2k views

SQL Server Point Constructor or version of ST_Point(x,y)?

PostGIS provides two point-constructors ST_MakePoint(x,y,[z,[m]]) ST_Point(x,y)or the compliant version that does not support 3DZ or 4D MySQL supports Point(x,y) In SQL Server, Is there anyway to ...
6
votes
2answers
666 views

Rebuilding a spatial index with minimum downtime

We just upgraded from SQL Server 2008 to 2014. It went fairly well except for a problem we have on a spatial index. On this table we receive the error Cannot insert duplicate key row in object 'sys....
6
votes
1answer
201 views

SQL Server - Using spatial data

I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user. The data is being sent from ...
6
votes
2answers
2k views

Over 99 million records with geography column & spatial index - VERY VERY VERY slow queries!

I’ve been working with SQL Server for a long time but have only recently encountered the need to work with spatial data. I have come into an environment that uses it pretty heavily and my first real ...
6
votes
1answer
330 views

sql server spatial index performance

I have a table with around 2 million records in it. I created a spatial index, using the defaults other than the bounding box. I've been noticing that some queries are extremely fast, and some are ...
6
votes
1answer
561 views

Permission to create Spatial Index

Permissions required to create a Spatial Index on a table, for this BOL says: Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the ...
5
votes
2answers
4k views

How can I speed-up my query on geo-location processes

I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query: SELECT *, point(45....
5
votes
2answers
13k views

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 ...
5
votes
2answers
1k views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
5
votes
3answers
449 views

Versioning: Is this technology used in DBMS other than spatial?

ESRI's spatial database management systems, called geodatabases (more), use a technology called versioning. A version represents a snapshot in time of the entire geodatabase and contains all the ...
5
votes
4answers
211 views

Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

I have a road_vertices table: create table road_vertices ( road_id number, vertex_index number, x number, y number ); insert into road_vertices values ('100',1,0,5); insert into road_vertices values ...
5
votes
2answers
2k views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
5
votes
3answers
2k views

How do I design a database for continents, countries, regions, cities and POIs?

I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, provinces)...
5
votes
1answer
91 views

Store both geometry and geography or store one and calculate the other?

I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (...
5
votes
1answer
2k views

Minimum distance of a location; one table of points compared to a set of points SQL 2012

I am trying to find the quickest way to find the minimum distance of one point to a table of points. The only caveat is that the table of the points I'm trying to find the minimum distance of is 150K ...
5
votes
1answer
263 views

Spatial data modelling tools [closed]

Is there anything like MySQL workbench for PostGIS? Something that supports SQL output and geometry types would be nice. CLARIFICATION: I am trying to implement the Land Administration Domain Model ...
5
votes
1answer
924 views

Optimizing an Intersect query between two huge spatial tables

I am having a hard time trying to improve an intersect between two spatial tables and I would like to receive any tips about the table designs, queries or dba configs. Tables: Table teste....
4
votes
1answer
909 views

How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points. However, for simple check of a point in a rectangle, it's ...
4
votes
3answers
465 views

Should I create a separate table for geo-location points?

I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup ...
4
votes
1answer
2k views

How do I render GeoHashes with PostgreSQL?

Are there any geohash implementation for PostgreSQL? I couldn't find any.
4
votes
1answer
346 views

How to store additional information about each point of a SQL Server geography line string?

I am using SQL Server 2016 and I have a Trajectory table and a TrajectoryPoint table. A trajectory consists of many trajectory points, and a trajectory point is related to only one trajectory. Each ...
4
votes
1answer
2k views

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
4
votes
1answer
133 views

Spatial query to get enclosing radius/polygon of result set

Is it possible in SQL to run a query of spatial data, and then just return a single polygon, or a radius, that encloses, aggregates, all of the spatial data that was in that query into one feature?
4
votes
2answers
296 views

Why the same execution plan causes LOB reads in some cases?

I have a spatial index and the following query: SELECT DS2.[ID] ,DS2.[InstanceID] ,DS2.[Location].STDistance(@Location) FROM [dbo].[DataSource01] DS1 INNER JOIN [dbo].[DataSource02] DS2 ...