Questions tagged [spatial]
Storing, querying, and indexing spatial data (including geometric and geographic representations).
254
questions
0
votes
0answers
13 views
Oracle Spatial function MDSYS.sdo_contains returns incorrect results
I use following SELECT statement to convert latitude, longitude to country code:
SELECT code
FROM country_boundaries
WHERE
MDSYS.sdo_contains(
boundary,
SDO_GEOMETRY(
...
1
vote
1answer
32 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);
--...
0
votes
1answer
18 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....
0
votes
1answer
28 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....
0
votes
0answers
36 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 ...
1
vote
3answers
167 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 ...
0
votes
1answer
20 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
33 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 (...
1
vote
0answers
67 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
40 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, ...
0
votes
2answers
210 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
4answers
42 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 ...
0
votes
0answers
38 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::...
1
vote
1answer
124 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 ...
1
vote
1answer
19 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 ...
0
votes
2answers
153 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
28 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 ...
1
vote
2answers
211 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 ...
0
votes
0answers
33 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 ...
1
vote
1answer
180 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 (...
2
votes
1answer
50 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))
...
3
votes
1answer
283 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 ...
1
vote
0answers
223 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, '...
0
votes
0answers
19 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 ...
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, ...
0
votes
0answers
48 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 ...
1
vote
2answers
331 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
55 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 ...
3
votes
3answers
800 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 ...
6
votes
1answer
288 views
MSSQL Geography STIntersects returns 1 when 0 is expected
I'm experiencing an MSSQL Geography issue I can't explain. Assume that I'm math-challenged, but it seems like the following point should NOT intersect the following polygon:
DECLARE @t_point ...
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 ...
0
votes
1answer
208 views
How to create Polygon from Point datatype in MySQL?
I have a table like this:
CREATE TABLE `aois` (
`aois_id` int(11) NOT NULL DEFAULT '0',
`WS_A` point DEFAULT NULL,
`WS_B` point DEFAULT NULL,
`WS_C` point DEFAULT NULL,
`WS_D` point DEFAULT ...
2
votes
1answer
162 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, ...
0
votes
1answer
665 views
Sharding a MongoDB instance by a spatial key and a date
This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
0
votes
1answer
47 views
Oracle GIS database replacement [closed]
My company has a huge (hundreds of millions of records) Oracle database of GIS/temporal information. We are trying to switch to an open source alternative.
If it helps, we imagined the following ...
2
votes
1answer
2k views
RDS: permission denied for relation spatial_ref_sys when copying
I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,
ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
5
votes
1answer
1k views
Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]
I am trying the following insert query on mysql 8.
Insert Into fence Set
fenceName='aa',
radius=2,
fenceGeometry=ST_GeomFromText('POINT(...
6
votes
1answer
240 views
How do I force SQL Server to use my spatial index through a view?
I have tables which holds transactions for properties that are stored as lat long pairs. (There are more columns and datapoints then in my example schema).
A common request is to find the ...
2
votes
1answer
181 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
1answer
107 views
Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs?
I am trying to understand geography data types so I can integrate my database more closely with GIS processes. From my reading, it appears that a geography field can hold many different types of ...
2
votes
1answer
148 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
132 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
61 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
1answer
79 views
SQL to find nearest location in one table from locations in another
I have a table like this, that contains a few million locations (POINT):
CREATE TABLE NewLocations(
[NewName] [varchar](20),
[NewLocation] [geometry]
)
I have another table like this, that ...
1
vote
1answer
348 views
MSSQL Spatial performance (point in polygon)
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per ...
2
votes
1answer
611 views
Is it possible to add a spatial index to a partition with Postgres 10?
I am creating declarative partitions like:
CREATE TABLE mydata.my_features (
id bigint,
my_feature1 character(1),
my_feature2 integer,
...
my_geom geometry
) PARTITION BY ...
0
votes
1answer
159 views
Does MySQL or MariaDB support a LineString constructor that takes points?
It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2)
> SELECT ST_MakeLine(Point(0,0), Point(0,...
0
votes
0answers
155 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......
2
votes
1answer
2k views
Longitude and Latitude order with ST_X, and ST_Y on SRID 4326
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the ...
