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

learn more… | top users | synonyms (3)

21
votes
1answer
303 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 ...
15
votes
3answers
2k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
11
votes
1answer
194 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 ...
10
votes
2answers
6k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

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 ...
10
votes
2answers
1k 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 = ...
8
votes
2answers
2k views

How do Application Like Yelp Retrieve distance information from data base efficiently

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, ...
7
votes
1answer
90 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
165 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
113 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 ...
6
votes
2answers
499 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
76 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, [...
6
votes
2answers
14k 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. ...
6
votes
2answers
416 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
104 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
481 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
1answer
7k 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) ...
5
votes
1answer
175 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 ...
5
votes
0answers
29 views

What Database is optimized for 4d data layout? [closed]

I need to store 4d data points inside a virtual 3d world (position+date+data value). I have a lot of data layers > 250 (wind direction, sea temperature, terrain type, etc). Sources of my data layers ...
4
votes
2answers
304 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 ...
4
votes
1answer
1k 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
177 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
4
votes
1answer
421 views

T-SQL and GPS co-ordinates

I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones. I've read about STDistance on MSDN, but it only appears to exist in SQL ...
4
votes
1answer
132 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 ...
4
votes
2answers
315 views

Geographic Hierarchies in a OLAP Cube / Data Warehouse

Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties ...
4
votes
1answer
213 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 ...
4
votes
3answers
992 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)...
4
votes
0answers
70 views

Can SQL Server use a spatial index and another index at the same time and be efficient?

Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below: CREATE TABLE [dbo].[DigitalTerrain]( [DigitalTerrainID] [uniqueidentifier] NOT NULL, [...
4
votes
0answers
317 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 ...
3
votes
3answers
104 views

Query Spatial Data for Nearest Neighbor for each group

I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location]....
3
votes
2answers
51 views

How to get the size of a spatial index?

I want to see the size of a spacial index. I am usually using one of the following code blocks to get the indexes sizes of a given table, but none of them is working for a spatial index. DECLARE @...
3
votes
1answer
480 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 ...
3
votes
3answers
822 views

Which graph database for search engine and gis?

I just learned there are "graph databases" out there, which are more suited for two applications "search engine" and especially "gis". To avoid discussions weather a relational DB is better suited ...
3
votes
1answer
761 views

Geohash implementation in PostgreSQL

Are there any geohash implementation for PostgreSQL? I couldn't find any.
3
votes
1answer
1k 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)...
3
votes
2answers
104 views

How to create a custom map report

not sure if this is the right forum. But just wondering is there a way one can make a map report out of an image or a map made on excel. I have the data and I've looked around and found that its ...
3
votes
1answer
465 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 ...
3
votes
1answer
724 views

Insert performance with Geography column

I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly. I started out with no ...
3
votes
1answer
1k views

MongoDB geospatial query with sort - performance issues

I have query (which is very slow ~2,5s): db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000) When I run explain for this query I get {...
3
votes
0answers
63 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....
2
votes
1answer
1k views

Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

Hopefully the question is fairly self-explanatory. I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the ...
2
votes
2answers
1k views

Error updating sql server geography type in trigger

I have the following trigger in my database: CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS IF UPDATE(Latitude) UPDATE pt SET pt.GeocodedAddress = ...
2
votes
1answer
286 views

Creation of spatial index locks the table for hours

I used this code to create a spatial index in SQL Server 2008 R2: BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ...
2
votes
1answer
167 views

App Domain unloaded when using GDAL/OGR ogr2ogr to insert many Geometry records

SQL Server 2012 x64, Total memory allocated 2GB, set to low value to enable us to reproduce "AppDomain unloaded" issues much quicker Windows Server 2012, Total Memory on Server 16GB (in production, ...
2
votes
1answer
44 views

SQL Server spatial geometry properties on index

Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012. I have mixed SRID table and I was wondering if it ...
2
votes
1answer
41 views

Geography data type and Upgrading from 2008 r2 to SQLServer 2014

I am getting a warning about a data type called geography. Our system has 1 column of data type geography. I am trying to figure out if the upgrade will fail or just if an app may have an issue. ...
2
votes
2answers
96 views

Geo localized database RTREE

I'm using MYSQL V5.6.24 GPL I have a database with 200K rows and growing and I'm trying to get the nearest, based on points. I have all the database in InooDB, and to use Spatial data, I create one ...
2
votes
1answer
110 views

How to get the spatial index in CREATE TO script?

Is it possible to get the spatial index using the create to script using SQL Server 2012? When I write out a table using SCRIPT TABLE AS > CREATE TO in Microsoft SQL Server Management Studio 2012, I ...
2
votes
1answer
2k views

What is the syntax for querying a spatial column?

What is the right syntax to get all the points within the bounding box? The table consists of a lat and lng column and a spatial column "pt" generated from the lat/lng values. calculated the topleft ...
2
votes
1answer
942 views

Visualiser for spatial query results

This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput. SQL Server ...
2
votes
1answer
64 views

What is the point of a SRID in a geometry type?

From my understanding, SRID is a projection code; how the 2d coordinate system is fit over a spheroid. The difference between Geometry and Geography types, from my understanding, is that a geometry ...