Questions tagged [spatial]
Storing, querying, and indexing spatial data (including geometric and geographic representations).
228
questions
1
vote
1answer
75 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
198 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
40 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
39 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
42 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
141 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
41 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
367 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
235 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(...
0
votes
0answers
36 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 ...
6
votes
1answer
89 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 ...
0
votes
1answer
78 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
45 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
46 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
59 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
36 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
46 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
168 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
98 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
98 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
82 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
668 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
0answers
165 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
69 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
130 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 ...
1
vote
1answer
484 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
396 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
216 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
280 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
188 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
241 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
146 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
64 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 ...
2
votes
1answer
253 views
PostgreSQL Geo Spatial Query is slow
After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only.
I need to fetch records from a Table ...
0
votes
1answer
436 views
What does MBRContains and MBRWithin test? What does “contains” and “within” mean?
MBRContains(g1, g2) is defined as,
Returns 1 or 0 to indicate whether the minimum bounding rectangle of g1 contains the minimum bounding rectangle of g2. This tests the opposite relationship as ...
5
votes
1answer
620 views
Does MySQL or MariaDB support GIS KNN Index Queries (k-nearest neighbors)?
Can you do k-nearest neighbors algorithm (k-NN) queries on MySQL for example, to find the nearest point in PostgreSQL with PostGIS, I can run a kNN query on the spatial index with <->
SELECT ...
7
votes
4answers
1k views
MySQL Geo Spatial Query is very slow although index is used
I need to fetch records from a InnoDb Table by distance (must not be exactly) and sort by distance.
The table has 10 million records.
My best time is so far 8 sec (3 sec without order by distance), ...
2
votes
1answer
2k views
How do I create a point with an SRID in MySQL?
MySQL supports using ST_PointFromText to construct a point with an SRID,
ST_PointFromText(wkt[, srid [, options]])
Constructs a Point value using its WKT representation and SRID.
This ...
2
votes
2answers
614 views
Search for rows with coordinates within a Polyline
I'm stuck with the logic of a MySQL query where I need to find if a row exists where:
The column longitude and latitude are within a Polyline
The column hit is duplicated within the last month
I've ...
2
votes
1answer
715 views
MongoDB Using String-Type Shard Key for Zone Ranges
Mongo lets any field type be used to set ranges for Shard Zones, but doesn't specify how analyses are conducted.
My goal is to point queries and writes at the exact right server, WITHOUT using a ...
2
votes
3answers
1k views
Find locations in table that are within a certain radius distance of a given latitude/longitude
We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value.
To get areas within 25 ...
2
votes
2answers
269 views
Storing point data for linestring in MySQL
Ok, i am have a need to store data on SOME points of a poly line but not others.
The current way I an doing it is i have 3 tables
line:
lineID INT AUTO INCREMENT
lineType INT
forkFrom INT
linePoint:...
2
votes
2answers
83 views
How to verify that at least one point fall into a polygon with Postgis?
I have two tables where table A has a geometry (4326) column that contains surfaces (~20M). A second table B has ~ 500K points (4326). What I want to do is "flagging" (adding a boolean column) every ...
3
votes
2answers
185 views
Joining tables based on time and distance: PostGIS with PostgreSQL?
I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small":
Small Table:
id event_time ...
1
vote
1answer
54 views
In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY?
MySQL has a function ST_SwapXY which swaps the x and y values (long, and lat). A function, like ST_SetSRID, that's useful if you import data incorrectly.
Accepts an argument in internal geometry ...
1
vote
1answer
126 views
Latitude & longitude universal format [duplicate]
I don't know yet what database I'll use in the future - MySql, Postgresql, Sqlite3 or anything else. At the moment I'm using a database N.
What's more or less universal approach for storing latitude &...
4
votes
1answer
111 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 ...
1
vote
0answers
119 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 ...
