Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
5
votes
3answers
483 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 ...
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 ...
1
vote
1answer
576 views

Mysql Count(*) incorrect value

Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the ...
4
votes
2answers
630 views

SQL Server spatial filter with additional conditions performance issue

I have a table Event that has 30 million rows and a table Location that has 35 million rows in SQL Server. An event can have multiple locations. I have a spatial index, geometry auto grid (cells per ...
2
votes
1answer
404 views

MySQL equivalent of a spatial ST_Union aggregate function?

I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function UnionAggregate, or the ...
0
votes
1answer
761 views

Spatial Data Cluster in sql-server ? ( like ST_ClusterWithin in Postgis )

Postgres 9.5 providing ST_ClusterWithin for Cluster the spatial data in DB level. Is there any available solution for data clustering in MS SQL (NOT SERVER CLUSTER) OR Is there Any Function ( like ...
1
vote
2answers
38 views

mysql query builder inefficiently builds a query

I have a track and a waypoint tables. The track table is also connected to a bunch more tables that are irrelevent. I wish to get all tracks, where at least one of their waypoints is in specified ...
4
votes
1answer
372 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 ...
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....
1
vote
1answer
465 views

SDO_GEOM.SDO_INTERSECTION between a line and a polygon returns what?

I'm confused as to what exactly the SDO_GEOM.SDO_INTERSECTION returns in Oracle. Documentation is here, but only gives examples of two intersecting polygons. What if I have many lines, some of them ...
3
votes
1answer
2k views

Why earthdistance <@> operator returns different value than earth_distance function?

Seeing a different value with <@> and earth_distance with points as ( select '(1,1)'::point as p1 ,'(2,2)'::point as p2 ) select ((p1 <@> p2) * 1609.34::double precision) as ...
2
votes
1answer
102 views

Seaching points within a radius - performance issues

i'm in trouble and completely lost with a geometry search, using 2 tables, linked by uid, each one contains about 5 millions rows. One table with address and point coordinates, Second with user status ...
8
votes
3answers
590 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 ...
2
votes
1answer
404 views

Why I can't calculate the distance between a fixed point and a geometry field of a MySql table using this distance function? [closed]

I am not so into database and GIS and I have the following problem. I have defined this function that return the distance between 2 points in the space represented by 2 point fields: CREATE FUNCTION ...
0
votes
1answer
86 views

GSE1015N A spatial reference system named “NAD83_SRS_1” does not exist [closed]

I am following this tutorial https://www.ibm.com/developerworks/data/tutorials/dm-1202db2spatialdata1/index.html And, running the sql "Listing 3. Contents of import_counties.sql" in IBM DB2 (on ...
2
votes
2answers
257 views

Tweaking indexes for tables with “geographical” data

We have quite a large table in our database which looks something like this: CREATE TABLE `nests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pokemon_id` int(11) NOT NULL, `user_id` int(11) NOT ...
2
votes
2answers
94 views

Elevation data in a database (tens of billions of information)

I have a need of a service which gives me the elevation for a coordinate (latitude, longitude). There is a dataset called SRTM3 where you have this information in a list of files. Each file contains a ...
1
vote
1answer
2k views

Is it possible to have a loop in SQL without creating a stored procedure?

I would like to loop through the vertices in a SHAPE/GEOMETRY column in a user-defined data type (ESRI's version of ST_GEOMETRY). I've posted the details in a GIS SE post here: https://gis....
1
vote
2answers
2k views

ST_Distance_Spheroid does not exist

I've try use MySql function ST_Distance_Spheroid for calculating distance but got following error : ST_Distance_Spheroid does not exist. Is something missing?
2
votes
2answers
70 views

Ways to invoke methods on CLR datatypes

I have difficulty understanding when to use .method() and when to use ::method() in SQL Server. For example For HierarchyID data type I have seen HierarchyID::GetRoot(), but also @sample_node....
4
votes
2answers
304 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 ...
20
votes
3answers
701 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/...
0
votes
2answers
204 views

Update all geometry points by adding/subtracting from XY

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
3k 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]....
-2
votes
1answer
82 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 ...
7
votes
1answer
3k 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, [...
1
vote
1answer
191 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....
1
vote
1answer
506 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 ...
3
votes
1answer
327 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. ...
3
votes
1answer
792 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, ...
4
votes
1answer
230 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 ...
5
votes
1answer
955 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
104 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? ...
3
votes
2answers
325 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 @...
2
votes
2answers
310 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
2answers
348 views

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

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
1answer
772 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 ] }...
23
votes
1answer
921 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
699 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 ...
1
vote
0answers
3k 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) ...
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 ...
1
vote
2answers
163 views

PostGIS radius query

I have a table setup with the following columns, and it contains about 100K listing of data. Column: address | Type: Text Column: geo_point | Type: geometry(Point,4326) ...
3
votes
0answers
122 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 ...
2
votes
2answers
4k 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
44 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 ...
4
votes
0answers
486 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
73 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
576 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('...
8
votes
1answer
19k 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
557 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 ...