Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
2
votes
1answer
549 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 ...
4
votes
1answer
267 views

Spatialite: use of virtual spatial index for distances

I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here http://www.gaia-gis.it/gaia-sins/spatialite-...
0
votes
1answer
47 views

Can SSRS XML data sources contain spatial data?

I've been given the task of producing a report which will extract data from a web service. The web service provides data from a list of sites including aggregates. Lets say total sales by month. ...
1
vote
1answer
2k views

Triggers on Insert or Update SQL Server (Spatial)

I am looking at creating a trigger on a live SQL Server spatial table that will update a column when a user either: Creates a new feature Updates the geometry of a feature in a GIS application ...
12
votes
1answer
260 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 ...
2
votes
1answer
873 views

rt_raster_to_gdal: Could not load the output GDAL driver

I have already set the environment in windows SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; Even I tried by adding following in environment path- POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL But ...
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 ...
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 ...
2
votes
2answers
365 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 ...
1
vote
1answer
121 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 = '...
7
votes
1answer
138 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 ...
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 ...
4
votes
1answer
136 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, [...
0
votes
1answer
56 views

Multi-master synching in three geographically separate datacentres

Our company needs to create an application that operates in all parts of the world. We are looking to have three servers (one in Europe, one in Asia and one in America). I'm looking to use multi-...
3
votes
1answer
348 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 ...
7
votes
1answer
600 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 ...
1
vote
1answer
2k views

Insert GEOMFROMTEXT (lat lng)

I am stuck, please help. The following SQL statement works: "INSERT INTO `routeplans` ( `polygon` ) VALUES ( GEOMFROMTEXT( 'POLYGON( (39.593254951279626 -119.2415714263916, 39.596231192849004 -...
0
votes
1answer
332 views

MongoDB Benchmark with write-intensive on geo-indexed DB

I am using PostgreSQL 9.3 on AWS RDS to store geo information into an indexed table of points. My data is write-oriented, meaning that I write nearly 1,000 points per each geo search query. I am ...
1
vote
2answers
458 views

Find points within a distance using MySQL

I have a MySQL table Users(State, City, Zip, Lat, Long) I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my ...
0
votes
2answers
132 views

Find the closest bar, when searching for a drink

Problem: Find the closest bar that serves the drink(s) I'm looking for. Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW The API Request that interacts with this problem will ...
15
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 ...
0
votes
1answer
91 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
0
votes
1answer
194 views

STDisjoint() = not STIntersects()?

In short, are STDisjoint() and STIntersects() antipodes of each other? According to the documentation, it seems that they are. But there is no explicit note about it. Are there any tricks? Which one ...
1
vote
2answers
926 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 ...
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 ...
1
vote
1answer
120 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 ...
2
votes
1answer
4k 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 ...
0
votes
1answer
46 views

Raster files into DB2

Team, We have been loading shapefiles into DB2 using db2se import utilities. I have been trying to find help to load Raster files and i wasnt able to find any help on this. it would be really ...
3
votes
2answers
3k views

Using AWS DynamoDB Geospatial index with Python

Amazon's AWS DynamoDB features Geospatial Indexing, which facilitates geo queries: Query Support: Box queries return items that fall within a pair of geo points that define a rectangle as ...
1
vote
1answer
104 views

Column not found in dbase file

When I try to import a shapefile using db2se, I specify the input columns to be loaded in the syntax as the COLUMN NAME db2se import_shape <dbname> -userId <username> -pw <password> ...
1
vote
1answer
221 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 U:\ShapeFiles\...
0
votes
0answers
51 views

Oracle spatial database

I am trying to run a simple query mentioned on: http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_operat.htm#g996876 SELECT c.mkt_id, c.name FROM cola_markets c WHERE SDO_COVEREDBY(c....
6
votes
1answer
338 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 ...
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 ...
4
votes
1answer
347 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
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 ...
3
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 ...
1
vote
0answers
174 views

Find the neighbouring polygons of a given polygon in a MySQL spatial table in ver < 5.6.1

Finding neighbouring shapes in MySQL spatial table made up of ID and 2D geometry (polygon) fields. I have a table with an ID column and a geometry column (holding a polygon) representing various ...
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 = ...
1
vote
0answers
241 views

Is there are way to use Fine Grain Auditing to record Varray bind values?

I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are ...
0
votes
0answers
189 views

Backup in Mysql periodically

I have trouble scheduling a backup in mysql periodically, most of the tools do not recognize the geometric field, for example navicat which says Can not get geometry object from data you send to ...
0
votes
0answers
547 views

Error creating spatial index sqlserver

I am creating spatial index on sqlserver table. CREATE SPATIAL INDEX [SIndex_udt_cell_SP_GEOMETRY] ON [udt_cell] ( [SP_GEOMETRY] )USING GEOMETRY_GRID WITH ( BOUNDING_BOX =(80.0713, 26.389581, ...
3
votes
2answers
9k 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 ...
2
votes
2answers
2k 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 ...
4
votes
1answer
2k views

How do I render GeoHashes with PostgreSQL?

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