Questions tagged [spatial]

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

42 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
0answers
537 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
336 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
143 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
160 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
131 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
121 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 ...
2
votes
1answer
37 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
249 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
47 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
457 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
79 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
0answers
66 views

Improving performance in a query with median

I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019) Actual ...
1
vote
1answer
15 views

Is there a plugin to add a map behind the spatial results?

Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a ...
1
vote
0answers
184 views

MySQL 8 - Optimize query using ST_Contains or ST_Within

I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows: UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, '...
1
vote
1answer
33 views

How to move geographical data from MS Access to SQL Server

For 20 years I've been working with MS Access, with much VBA involved. I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, ...
1
vote
2answers
235 views

MySQL 8 not using Spatial Index?

I have an issue where whatever I do I cannot get MySQL to use a spatial index. A query like the following then takes 5 seconds! Any way to force this to use the index at all? WITH ranked_reports ...
1
vote
0answers
47 views

MySQL 8.0.18 - ST_Area returns invalid value

I have a problem with ST_Area function in MySQL 8.0.16. The result of the procedure call returns a different result than any library used to calculate the area of a polygon. Example: I have a ...
1
vote
0answers
19 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 ...
1
vote
1answer
158 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 ...
1
vote
0answers
59 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
158 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
289 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
35 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
216 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
199 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
246 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
90 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
22 views

Cannot use 'STWithin' as it can't be found for type 'Microsoft.SqlServer.Types.SqlGeography' (SQL Server V14)

I am currently using the STIntersects method to spatially match a geographic point to a geographic area. I am working with Geography types here not Geometry. Historic code from the place I work shows ...
0
votes
1answer
15 views

pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images. A dump of the database was taken from the first ...
0
votes
1answer
26 views

How to load geometry point tables in sql spatial

Need help loading point data in sql spatial. Similar to this post but I really dont know how to get XY data to be accessible by sql I want to do what was done in the initial part of this post (...
0
votes
2answers
158 views

Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?

I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column. Options for the GEOMETRY column datatype include: ESRI's proprietary ...
0
votes
0answers
36 views

How to fill the gap in a multipolygon in SQL Server

Using SQL Server 2014. I have the following self-intersecting geometry that I make valid with .MakeValid() which gives a multipolygon. declare @geomOrig geometry select @geomOrig = geometry::...
0
votes
2answers
123 views

Improving Speed of Cross Apply

I posted a question on how to create a relation between 2 tables based on a spatial field https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering The performance is ...
0
votes
0answers
25 views

TSQL Join on Nearest (tables have no relationships)

I have 4 tables Listings Cities Regiions Countries Listing is not related to others at all, but cities, regions and countries have FK. I would like to get a list of Listings but also include in the ...
0
votes
1answer
27 views

Get count of near points based on 2 unrelated tables

I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on. They both have a geography field. I would like to get list of all ...
0
votes
0answers
23 views

MySQL performance issue with ST_Contains not using spatial index

We are having what seems to be a fairly large mysql performance issue on trying to run a fairly simple update statement. We have a table(1.8mil) with houses that contains a Lat+Long geometry point ...
0
votes
0answers
18 views

PostGIS buffer in miles not lat/long degrees

I am trying to take two points, add a buffer around them (in meters), draw a line between them, draw a buffer around that, and then query whether items are inside the area of the circles expanded line ...
0
votes
0answers
145 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
224 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
554 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, ...