A spatial data infrastructure (SDI) is a data infrastructure implementing a framework of geographic data, metadata, users and tools that are interactively connected in order to use spatial data in an efficient and flexible way.

learn more… | top users | synonyms (3)

12
votes
3answers
1k 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 ...
10
votes
2answers
5k 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 ...
9
votes
2answers
354 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
60 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 ...
6
votes
2answers
387 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
2answers
7k 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
1answer
388 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
5k 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) ...
4
votes
2answers
130 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
299 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
185 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 ...
3
votes
1answer
389 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
598 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
771 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 ...
3
votes
1answer
94 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 ...
3
votes
1answer
126 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 ...
3
votes
1answer
198 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
526 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
1answer
159 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 ...
3
votes
3answers
762 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, ...
3
votes
0answers
55 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, ...
3
votes
0answers
104 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
0answers
70 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 ...
2
votes
2answers
702 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
275 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
454 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
1answer
366 views

Geohash implementation in PostgreSQL

Are there any geohash implementation for PostgreSQL? I couldn't find any.
2
votes
2answers
27 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
46 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
941 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
552 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
302 views

Problem with shortest distance calculation in PostGIS

While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function. The logic is based on start_point(Start_id) and end_point(end_id) ...
2
votes
1answer
661 views

How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB)

My team need a DBMS like DynamoDB to store large amount of data, principally places and coordinates. I've considered to use some GIS-based DBMS (like PostGIS) with an index on the POINT, but DynamoDB ...
2
votes
2answers
328 views

How do I set a geography point to be the North Pole?

I tried to execute this query on my SQL Server 2008 R2 instance: UPDATE ITEMS SET GEOG=geography::STPointFromText('POINT(0 90)', 4326); SQL Server returned the following error: ...
2
votes
1answer
178 views

Scaling of PostGIS vs MySQL cos/sin calculations [closed]

I need to sort database rows according to GPS coordinates (or any other geographical representation). The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
2
votes
0answers
207 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 ...
1
vote
2answers
300 views

PostgreSQL : Is this because of locks(row level)?

I have a table with approximately 2.1 million tuples. Which has latitude and longitude columns. I am trying to convert this into a geographic type (point with SRID). The function(procedure) I have ...
1
vote
1answer
95 views

How can I improve this update?

I'm working with SQL Server 2014 Express edition and I want to upload some OpenStreetMaps data. Data is a 172 Mb XML and it loads ok. The problem is with this script: UPDATE ways SET geog4326 = ...
1
vote
1answer
205 views

Storing latitude and longitude

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 ...
1
vote
1answer
1k views

$nearSphere returns too many data. What am I missing? Am I wrong? Is it a bug? Does other see the same thing?

I noticed that $near and $nearSphere returns completely different data and I got suspicious. After all, when latitude is near equator it sphere distance shouldn't differ a lot from euclidean distance. ...
1
vote
2answers
208 views

how to design a database's table to store mysql spatial data in a parent child relation?

I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon. Examples: 1. World 1.1 US 1.2 China 1.3 Japan 1.4 South ...
1
vote
2answers
2k views

What is best way to storing geo coordinates related to planned route?

Here is my scenario: Application "A" produces lat and long points that correspond to planned route. Depending on route distance it could have several thousands of points. I know of Geography and ...
1
vote
2answers
127 views

Trying to create PostGIS database

I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation. In the short version, i executed, createdb yourdatabase createlang plpgsql yourdatabase But I did ...
1
vote
1answer
53 views

Spatial Index ignored when using additional non-spatial conditions

Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if ...
1
vote
1answer
513 views

MySQL Syntax - Searching points within polygons using GeomFromText

I am trying to select points within polygons using the GIS/spatial extensions for MySQL. I'm fairly new to this, so bear with me if this is an elementary question. I have points defined as text ...
1
vote
1answer
89 views

Spatial Loader DB2 giving error while importing shape file

While I am trying to import a shapefile using db2se import_shape file utility, db2se import_shape <dbname> -userId <userid> -pw <password> -fileName ...
1
vote
1answer
787 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
793 views

GeomFromText in trigger has error

I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ; when I insert a ...