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

learn more… | top users | synonyms (3)

0
votes
1answer
15 views

update all geometry points by adding/subtracting from XY in mysql

I want to update the POINT geometry column for all rows in a table. For example, I want to change POINT(1 1) to POINT(1.1 .9) UPDATE myTable SET geoPoint = asText(geoPointX+1 geoPoint Y-1) The ...
3
votes
3answers
103 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
1answer
66 views

Why do we have Geometric Datatypes in SQL databases? [closed]

What is the purpose of a specific SDI datatypes in SQL tables? They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them? What ...
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, [...
0
votes
0answers
42 views

How can I detect if a function is built in to MySQL/MariaDB?

Is there a way I can detect if a function is built in, in MySQL/MariaDB? I'd rather use feature detection than version string matching in my application. I know about the responses here for user ...
1
vote
1answer
41 views

Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

I have two points and I have to calculate difference between them. I using below query in SQL Server. SELECT geography::Point(27.185425, 88.124582, 4326) .STDistance(geography::Point(27....
0
votes
1answer
75 views

How do I find the cumulative distance between geometry points in SQL Server 2014? [closed]

I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is ...
2
votes
1answer
39 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. ...
0
votes
1answer
31 views

Understanding geospatial queries in mysql [closed]

I asked this over on stackoverflow but haven't got a response, but I think this community might be better placed to answer I'm attempting to get my head around geospatial indexes in mysql and I'm ...
2
votes
1answer
166 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, ...
1
vote
0answers
46 views

The definitive fastest radius search query with Postgis 2.0

I come from mysql world. I read the manual and saw some examples, after that, I'm not able to say witch method is the fastest for a radius search. Nobody seems to agrees. My simple case: I need to ...
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 ...
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....
0
votes
1answer
19 views

How do I create a new mongo collection with custom indices from an existing mongo db? [closed]

I have a mongo database collection, that I want to modify. I would like to add new indices (text and geo) to the existing data, and remove unnecessary fields. What would be the best way to do this? ...
0
votes
0answers
29 views

N-dimensional vectors in PostgreSQL Spatial Index

I have many 8-dimensional vectors. And I want to construct on them a R-tree ('USING gist' in PostgreSQL). But I have got stucked at the choice of the correct geometrical type. Whether the 'path' type ...
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 @...
1
vote
1answer
64 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
1
vote
0answers
31 views

implement geo / spatial / proximity search with php and mysql — what have you done successfully

For my website's users, I want to implement a worldwide geo / spatial / proximity search with php and mysql. I want to do this as simply and minimally as possible. What is the minimum data I should ...
0
votes
0answers
35 views

Why is my spatial index not being used on Mariadb?

I have a spatial index on geo column, but when I do EXPLAIN it doesn't show that the index is being used. Can anyone tell why? EXPLAIN SELECT AsText(geo) FROM marker WHERE ST_Distance(POINT(-94....
0
votes
1answer
61 views

Do mongodb's 2dsphere index works on multidimensional coordinantes array?

if i create a index like that : db.createCollection("users") db.users.ensureIndex({coordinates:"2dsphere"}); and then insert a point var point = { type: "point", coordinates: [ 1, 2, 3, 4 ] ...
21
votes
1answer
302 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 ...
7
votes
1answer
164 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 ...
0
votes
0answers
1k views

MySQL 5.7 InnoDB is extremely slow on spatial data compared to MySQL 5.6

I have a table area which contains ~300,000 rows. It is defined as follows: CREATE TABLE `area` ( `area_id` bigint(20) NOT NULL AUTO_INCREMENT, `boundary` geometry NOT NULL, `name` varchar(255) ...
4
votes
1answer
128 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 ...
0
votes
1answer
42 views

Postgis radius search

I have a table setup with the following columns, and it containts about 100K listing of data. Column: address | Type: Text Column: geo_point | Type: geometry(Point,4326) My question is for ...
1
vote
0answers
31 views

PostGIS radius search performance

In my Postgres database, there is a listing table, inside that table, I have a column called geo_point, which stores latitude and longitude information. The column type is geometry(Point,4326). I ...
2
votes
0answers
52 views

Access points of geometric path data in PostgreSQL 8.1

Summary: I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to ...
0
votes
0answers
29 views

Performance issues when using 2dsphere indexes

I'm working on mongodb for geospatial processing. Currently I'm ingesting about 8 million GeoJSON, that initially were stored as point data, taking about 20 minutes to index. Recently, we changed the ...
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 ...
1
vote
2answers
258 views

Store geo location data in MySQL

I would like to know how should i store longitude and latitude values in a mysql database, maybe the questions should be: Decimal vs Point (Spatial Data) I found this question where the question is ...
1
vote
1answer
38 views

Advantages of POINT over DOUBLE for distance queries?

I'm about to start an application that will need a simple distance query to order results. After some search I've discovered some functions that seems to do this work pretty easily without needing ...
2
votes
0answers
73 views

SQL Server 2012 duplicate key error with Spatial index that shouldn't be unique

We have a table that has a geometry column that is filled by a stored procedure from two x and y values whenever they're changed ( SW_GEOMETRY = GEOMETRY::STGeomFromText('POINT('+ CONVERT(VARCHAR(50)...
2
votes
0answers
53 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for "...
1
vote
2answers
75 views

Read GPS Coordinates into LineString from Table

I found this beautiful piece of code which takes a linestring and I can get the points from it: DECLARE @GeographyToConvert geography SET @GeographyToConvert = geography::STGeomFromText('...
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)...
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 ...
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 ...
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 ...
2
votes
0answers
77 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
30 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. ...
0
votes
1answer
235 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 ...
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 ...
1
vote
0answers
137 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 ...
6
votes
2answers
412 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
95 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
102 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
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 ...
0
votes
1answer
3k 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 ...
4
votes
0answers
69 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
43 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-...