Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
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 ...
0
votes
1answer
81 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
23 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
2answers
32 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
12 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
43 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
47 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)) ...
2
votes
1answer
70 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 ...
0
votes
0answers
58 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
16 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
28 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
44 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 ...
0
votes
0answers
40 views

Mysql8 ST_GeomFromGeoJSON(geo_json) throws error Longitude -180.000000 is out of range in function st_geomfromgeojson

I have a table with polygons stored as geojson. CREATE TABLE `location_boundaries` ( `id` INT UNSIGNED NOT NULL, `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', `...
1
vote
2answers
84 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
35 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
360 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
229 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 ...
0
votes
0answers
50 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 ...
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
47 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
73 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
324 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
43 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
895 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
3
votes
1answer
589 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
136 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 ...
1
vote
1answer
120 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
57 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 ...
1
vote
1answer
67 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
73 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
45 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
59 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
253 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
242 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
129 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
116 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
1k 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 ...
1
vote
1answer
231 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: ...
0
votes
1answer
82 views

Do the type-specific WKT- constructors function differently from ST_GeomFromText?

From the docs on MariaDB, for ST_LineStringFromText Constructs a LINESTRING value using its WKT representation and SRID. ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ...
0
votes
1answer
190 views

How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB?

Let's say I have a table with 3 points, CREATE TABLE foo ( id int PRIMARY KEY, geog point ); INSERT INTO foo (id, geog) VALUES ( 1, Point(0,0) ), ( 2, Point(1,1) ), ( 3, Point(2,2) ); How can ...
2
votes
1answer
837 views

ERROR: invalid GeoJson representation

the GeoJSON Spec show, { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" } } But when I wrap that in ...
1
vote
1answer
531 views

MySQL 8 Geospatial Queries, MakeEnvelope Workaround?

I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored. Using that data, I have the following query... SELECT ...
0
votes
1answer
348 views

How do you create a Geographic Point with MariaDB and MySQL?

PostGIS provides a simple cast to construct a geography type from floating point lat, and long SELECT ST_Point(1,2)::geography; -- standard version (MM) less precision SELECT ST_MakePoint(1,2)::...
0
votes
1answer
394 views

Does HBase support spatial functionality?

I see mention for spatial functional in HBase. For example "HBaseSpatial: A Scalable Spatial Data Storage Based on HBase". What spatial functionality does HBase support and where is this documented?
1
vote
2answers
83 views

Joining relations based on distance between geometry columns?

I have two tables: User(id) Region(id, userID, origin Geometry('point'), radius)) User has a one-to-one relationship with Region. I want to query all users that have a region that contains a point (...
1
vote
2answers
232 views

Testing Geometry Polygon Data is Valid Using a Stored Procedure

We are using a SQL Server Spatial database with the following table setup: SRID of 27700 geometry_column of ogr_geometry geometry_type of POLYGON I am using a SQL Server stored procedure to perform ...
3
votes
1answer
283 views

How can I search for all points with a specific range (radius) of another point?

The syntax I use to query for polygons in range of other Points SELECT * FROM dbo.myTable INNER JOIN dbo.myPOI ON myTable.geog.STIntersects(myPOI.geog) = 1 But, to query points of interests, ...
1
vote
2answers
195 views

How can I use Spatial Indexes for timestamps (non-spatial 1d data)?

I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range: SELECT * FROM t_tbl WHERE ts BETWEEN @someDate1 AND @someDate2 I want to make this ...
1
vote
1answer
68 views

MBRCovers return true even though point outside the polygon

I'm having big trouble figuring out why the MBRCovers is returning true even tough the point I give as a parameter is outside the polygone supplied. The point is to find if a row has it's coordinates ...

1
2 3 4 5