Questions tagged [spatial]

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

32 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
4
votes
0answers
483 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)...
4
votes
1answer
268 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-...
4
votes
1answer
94 views

CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

@@Version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a ...
3
votes
0answers
120 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
1answer
39 views

Efficient way to store geographical routes in MongoDB

I need to store a taxi route into MongoDB, however I'm not sure what is the best way to do this. Since the route is only needed for the taxi order, the first idea I had is to store it in the order, ...
2
votes
1answer
36 views

Adding coordinates to table (forcing 1:1 association without unique identifiers on either side)

I have a table (around 60-80 million rows) in a postgresql database, that identifies census tracts that the observation belongs to (identifies an area of the US). I want to place a random point ...
2
votes
0answers
207 views

Table design pattern that can support partitioning, clustered columnstore index, and spatial data?

I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day). Requirements: Spatial ...
2
votes
0answers
40 views

Spatial index on raster tiles not used

I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat,...
2
votes
2answers
301 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....
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
1answer
38 views

Filtering a MySQL table on multiple columns, including one with spatial data

I have a MySQL table with linestrings: CREATE TABLE edges ( id int(11) unsigned NOT NULL AUTO_INCREMENT, geometry linestring NOT NULL, type_key varchar(255) NOT NULL, created_at datetime NOT ...
1
vote
1answer
50 views

SDO_NN the second nearest neighbor

Oracle SQL geometry allows me to find the nearest neighbor using SDO_NN(neighbors.GEOLOC,p.GEOLOC,'SDO_NUM_RES=1 UNIT = MILE',1) = 'TRUE') How can I get the select the second nearest point?
1
vote
0answers
35 views

Spatial index unused on nested set

I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any ...
1
vote
0answers
147 views

How to group-by to get MultiPoint/GeometryCollection in MySQL?

I have a table x like this: Columns: SubjectID int(11) Timestamp bigint(20) fix_geom point When i try to group my fix_geom column to a MultiPoint (or GeometryCollection) by SubjectID, like: ...
1
vote
0answers
110 views

Poor UnionAggregate (CLR aggregate) performance - CPU cost hint?

I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance. The following query takes quite a ...
1
vote
2answers
225 views

Should I join all of my polygons to make a single polygon for point-in-polygon search?

I have a set of of polygons. Sometimes they are completely disconnected (like separate patches). Other times, two sides of two polygons may touch each other (when the polygons are adjacent like on a ...
1
vote
1answer
31 views

How to relate LatLong and State columns on PowerBI

I have two separate tables, one with latitude and longitude columns (for locations in the USA), and the other with the US state abbreviation (TX, AZ, CA, etc). Is there a way to count the number of ...
1
vote
0answers
176 views

Partition of tables with constraint exclusion doesn´t optimize queries

Over an existing table in PostgreSQL (version 9.0.2) I created nine others which inherited the former with check constraints. I turned on the constratint_exclusion variable and runned some queries, ...
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) ...
1
vote
0answers
175 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 ...
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 ...
1
vote
0answers
112 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE -6....
1
vote
0answers
86 views

sdo_tune.quality_degradation returning negative values

While running the sdo_tune.quality_degradation function on one of our Oracle databases most of the results were around 1 but a number had values like -556127.225. Does this mean that these indexes ...
0
votes
0answers
36 views

Visualization Of Graph Data With Spital Drawing SSMS

I want to create a visualization of my graph database utilizing Spatial Drawing within SSMS. How do we connect the various polygons with node names and to from pointers? The layout should preferably ...
0
votes
0answers
16 views

Reducing contiguous values in geographical data to a set of polygons?

I've got a MySQL table with about 200,000 rows in it. The table has four columns, lat, lng, and value. value is just a percentage, and represents your chances of seeing an aurora in the night sky for ...
0
votes
0answers
29 views

How to create an Index on a raster column?

In a PostGIS enabled DB, I have a 1.7GB table with a raster column rast. I need to create an index on that column in order to speed up my raster queries. I have tried the following: CREATE INDEX ...
0
votes
1answer
67 views

Spatial Query using MySQL 8.0

I have a database in MySQL 5.7 where I create a Lat/Long column using "POINT", then I create a Index on this column with main key as second column. So my database is like PID, SurveyID, GeoPoint ...
0
votes
0answers
66 views

SQL Server - Cannot reorganize index on “extended_index” table

I have a maintenance plan that includes an index reorganization step. I am getting the following error in the job history: Executing the query "ALTER INDEX [SpatialIndex-20180705-165942] ON [dbo......
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....
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
548 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, ...