Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
22 views

Calculate distance (km) between point within multipolygon and nearest edge

I have MariaDB with geographic points and multi-polygons representing country borders. I need to calculate the nearest distance between one point within the country and its closest border. My example ...
Kiew's user avatar
  • 1
0 votes
1 answer
86 views

How to optimize spatial join?

The SQL Server is pretty fast finding neighbors of a geography in a spatial index. Either it is finding nearest neighbors or all neighbors in specific area, the query result is instant. I am not able ...
gotqn's user avatar
  • 4,016
0 votes
0 answers
41 views

Error importing a shapefile into DB2 spatial table using db2se import_shape

For a database (DB2 spatial is already enabled for it), I created a relation with the following attributes: CREATE TABLE state.village ( zipcode varchar(5), shape db2gse.st_multipolygon ); ...
Sirjon's user avatar
  • 165
0 votes
1 answer
40 views

Is there a way to automate back-up on SQL Express LocalDB?

I am new to the gis world, and my boss wants me to find a way to automate the process to create back-ups on a local SQL Express DB. I know that I can create one for nonlocal DBs, however, I was ...
user avatar
1 vote
1 answer
50 views

QGIS ODBC connection to shared Access database only working when Access closed at QGIS start-up

I am working with an MS Access database which is located on a shared folder and regularly used by other users at the same time (the database is split into several user front-ends and a back-end). I ...
Matt B's user avatar
  • 11
0 votes
1 answer
64 views

Improving the performance of STEquals

We have a table containing all polygons (geometry/geography) of the objects in our application. This is because most polygons need to have a 'geometry' (RDNew) and a 'geography' (WGS84) version of the ...
R. Hoek's user avatar
  • 119
0 votes
0 answers
61 views

Connection of database in QGIS with the SSH tunnel port like doing in postgreSQL maestro

How to connect the PostgreSQL database in QGIS with the SSH tunnel port like doing in PostgreSQL maestro? SSH tunnel has different ports and host the database has different ports and host Also, Both ...
Hasan's user avatar
  • 1
0 votes
2 answers
368 views

Unable to Create Fast Refresh Materialized View with Geometry Field

I am receiving the following error while trying to create a materialized view with a geometry field on Oracle (21c): ORA-12015: cannot create a fast refresh materialized view from a complex query I ...
salgado's user avatar
3 votes
1 answer
125 views

Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column

I have a table of frequently updating events that affect certain geometries. The geometry areas are fixed, but there may be an event affecting multiple areas at once, so there's a many-to-many ...
wxdan's user avatar
  • 33
0 votes
0 answers
26 views

Why is GIS WHERE clause costing me so much?

I've got this view that has problems with performance, at the moment I use st_intersects to filter this data, something that's a necessary part of the functionality, I need to be able to see that a ...
Hannes Colt's user avatar
0 votes
1 answer
890 views

Error Code: 3548. There's no spatial reference system with SRID 1498173264 - MySQL

I am trying to run a mysql query below: select * from geo where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609 where geometry is of type Polygon. and get an error: Error ...
kms's user avatar
  • 129
0 votes
1 answer
434 views

Optimizing geolocation query in Postgres with earth_box and earth_distance

I'm trying to optimize my geolocation query for a table of addresses of ~862k rows within a search radius using earth_box. My first initial query is not too terrible: explain analyze SELECT id ...
blin's user avatar
  • 3
1 vote
1 answer
339 views

extracting ordered list of spatial points from JSON into PostgreSQL array

i have JSONB data in PostgreSQL that looks like the following: {"datasetGPolygon": [ { "datasetGPolygonOuterGRing": { "gRingPoint": [ { &...
rocketboy2000's user avatar
1 vote
2 answers
132 views

db<>fiddle (Oracle): Query works in 18c, but not 21c

I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines: WITH lines AS (SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 ...
User1974's user avatar
  • 1,457
0 votes
2 answers
32 views

Database design - GIS point data

I am considering whether I should add columns to my existing tables or use a new table. The database is about birds, there locations over time and there nesting locations. The data that I need to ...
Al Grant's user avatar
  • 101
0 votes
1 answer
43 views

How to request an enhancement to SDO_GEOMETRY / Oracle Spatial

I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial. Example: Requesting a function to select vertices, including the multi-part numbers of SDO_GEOMETRY ...
User1974's user avatar
  • 1,457
0 votes
0 answers
106 views

SQL Server 2019 Express for Linux on Ubuntu 20.04 - Bug - CU 13 - c0000005 EXCEPTION_ACCESS_VIOLATION when specifying @wkt in Table-valued function

Pretty sure I've found a bug in SQL Server 2019 (Express) for Linux (CU 13, version 15.0.4178.1 running on Ubuntu 20.04 LTS). After the following table-valued function dbo.bTest is created: use devdb ...
Rayner's user avatar
  • 121
0 votes
2 answers
149 views

Select SDO_GEOMETRY line vertices as rows -- using recursive WITH clause

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) ...
User1974's user avatar
  • 1,457
1 vote
1 answer
1k views

Why is my spatial query slower in Postgres 13 than in Postgres 11?

Postgres versions PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
Setop's user avatar
  • 113
1 vote
1 answer
93 views

Is it the right scenario to use spatial index?

I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon. Here's the example of the query ...
Jozef Podlecki's user avatar
0 votes
1 answer
543 views

Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)

Oracle 18c: In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line: Select SDO_GEOMETRY line vertices as rows ...
User1974's user avatar
  • 1,457
0 votes
1 answer
134 views

Select SDO_GEOMETRY line vertices as rows

I have an Oracle 18c table that has an SDO_GEOMETRY column (lines): create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry); insert into a_sdo_geometry_tbl (line_id, shape) ...
User1974's user avatar
  • 1,457
0 votes
1 answer
263 views

MySQL table + partitioning + spatial data

We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
John C's user avatar
  • 233
3 votes
1 answer
1k views

TEXTIMAGE_ON and Computed geography column

There is an unusual behavior I need help understanding. Using SQL Server 2019 Express (v15.0.2080.9) and Management Studio. Here is the test I'm running: Create a table with a geography column. This ...
user avatar
2 votes
1 answer
405 views

Data truncation on spatial query after updating from MySql 5.7 to 8.0

I have the following table: create table places ( id bigint auto_increment primary key, position point SRID 4326 not null ); create spatial index position on places (position); --...
theberzi's user avatar
  • 151
0 votes
1 answer
28 views

A column of database type "geometry" results in two extra columns hidden from ssms

If I create a table like create table test2 (foo geometry) foo will show up in the table designer as expected but when I query sys.columns for column information... select t.name as TABLE_NAME, c....
Anders Lindén's user avatar
0 votes
1 answer
478 views

Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID

When I create a spatial index I can specify some extra parameters, e.g. CREATE TABLE dbo.Test( Id bigint not null, Polygon geography not null ) GO CREATE SPATIAL INDEX IX_Test_Polygon ON dbo....
Konstantin Konstantinov's user avatar
0 votes
0 answers
404 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 ...
Columbo's user avatar
  • 123
1 vote
3 answers
609 views

Query very slow when using spatial with radius

I have the following statement which is very slow (takes 6-30 seconds to get 21 records among 800k) using SQL Server 2019 Standard running 32GB ram (server does other things too) with 6 cpu cores ...
Zoinky's user avatar
  • 359
0 votes
1 answer
353 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 ...
Nyxynyx's user avatar
  • 1,121
0 votes
1 answer
306 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 (...
Tom's user avatar
  • 1
3 votes
2 answers
143 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 didn't suggest any new indexes, so I am looking for some help to improve it. (using SQL Server 2019) Actual ...
Zoinky's user avatar
  • 359
1 vote
1 answer
358 views

Searching for a close numeric match on spatial coordinates

I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as NUMERIC(9,6). If I run a query looking for an exact position match, ...
ConanTheGerbil's user avatar
1 vote
2 answers
838 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 ...
User1974's user avatar
  • 1,457
0 votes
4 answers
56 views

Postgis reporting a point is 1216m away from itself using ST_Distance [closed]

I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column ...
khafka's user avatar
  • 79
1 vote
1 answer
414 views

How to speed up SQL Server 2014 Spatial functions

I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database. Note that I have simplified the names of the tables and columns. To illustrate I have ...
carens's user avatar
  • 141
1 vote
1 answer
128 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 ...
Jonas Stawski's user avatar
0 votes
2 answers
1k 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 ...
Zoinky's user avatar
  • 359
0 votes
1 answer
34 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 ...
Zoinky's user avatar
  • 359
1 vote
2 answers
2k views

Geo Route - Best way/db to store route path

We are developing an app in that UI need to show a path where a truck driver drives the truck. Driver mobile will continuously send (example every 10 seconds) their live location. Which database is ...
Ankur Loriya's user avatar
1 vote
1 answer
1k views

Why does MySQL think I'm using SRID 1?

I can run the query on my local machine no problem (Server version: 10.4.6-MariaDB), but when I run it on my remote one (Server version: 8.0.17 MySQL) it poops out this error message: ERROR 3548 (...
user24601's user avatar
  • 503
2 votes
1 answer
160 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
Mark Stephenson's user avatar
3 votes
1 answer
1k views

How to add Spatial Reference ID 28992 to MSSQL server?

I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server? My end goal is to get this dataset into MS SQL Server, with the Geography Markup ...
Jens Wagemaker's user avatar
2 votes
0 answers
528 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, '...
adam78's user avatar
  • 155
0 votes
0 answers
75 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 ...
flanders9er's user avatar
1 vote
1 answer
86 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, ...
SWP_IB's user avatar
  • 11
0 votes
0 answers
49 views

In 8.0.17 EPSG Spatial reference systems are not available at installation? [duplicate]

In 8.0.17 INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS is not prepopulated with ORGANIZATION="EPSG" records, leaving the table with only a single record, that for the default SRID=0. A count in ...
ClearCrescendo's user avatar
3 votes
3 answers
2k 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 ...
Lee Armstrong's user avatar
1 vote
1 answer
151 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 ...
arkus's user avatar
  • 111
3 votes
3 answers
1k views

RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table

On aws RDS I have an mysql 8.0.16 instance running. My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty. This is a problem for me because I would like to create ...
Jonas Taulien's user avatar

1
2 3 4 5 6