Questions tagged [spatial]
Storing, querying, and indexing spatial data (including geometric and geographic representations).
0
votes
1answer
9 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
24 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
17 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 ...
0
votes
0answers
22 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
36 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
22 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 ...
0
votes
1answer
23 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
44 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
37 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
30 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
68 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
43 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
57 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
65 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
46 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
89 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
191 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 ...
3
votes
1answer
144 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 ...
4
votes
4answers
428 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), ...
1
vote
1answer
428 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
221 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 ...
1
vote
1answer
232 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
557 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
103 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
46 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
85 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
32 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
79 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
62 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
60 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 ...
7
votes
2answers
63 views
How to setup solution for multi-dimensional distance search with SQL Server spatial data types
I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows.
...
1
vote
2answers
87 views
Slony Replication in PostgreSQL
Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other
I am new in DBA and we are ...
0
votes
2answers
144 views
Use GROUP_CONCAT() to generate polygons in mysql
I have a table that has columns zone(name of the zone which is a string), longitude (decimal), latitude (decimal), logical order (integer).
logical_order indicates the order in which the coordinates ...
4
votes
1answer
76 views
Spatial query to get enclosing radius/polygon of result set
Is it possible in SQL to run a query of spatial data, and then just return a single polygon, or a radius, that encloses, aggregates, all of the spatial data that was in that query into one feature?
2
votes
1answer
77 views
For Postgres, efficiently get points within X distance of point Y?
I have this table as below in Postgres:
create table locations
(
id serial not null
constraint games_pkey
primary key,
name varchar,
location point,
)
;
I want to ...
2
votes
1answer
484 views
Sql Server (2017?) Lat/Long index and query
TLDR: Given a set of LAT/LON => index and return "nearby" results.
I'm working my way through a MEAN Node.js/MongoDB/Express/Angular book. It's definitely not something I'm comfortable with - I'm ...
4
votes
3answers
224 views
Should I create a separate table for geo-location points?
I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup ...
1
vote
2answers
90 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 ...
0
votes
1answer
41 views
How come spatial objects can't be used in a VALUES statement? [closed]
How come you can do,
SELECT *
FROM (VALUES
('foo') -- or (1), etc.
) AS t(x);
But, not
SELECT *
FROM ( VALUES
( geography::Point(0,0,4326) )
) AS t(geog);
I was trying to answer this ...
2
votes
1answer
28 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 ...
3
votes
1answer
553 views
&& vs ST_Intersects performance
I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ...
1
vote
1answer
239 views
What is PostGIS doing with ST_DistanceSphere?
PostGIS ST_Distance_Sphere and ST_DistanceSphere return the same thing in PostGIS 2.2.
ST_DistanceSphere says
Uses a spherical earth and radius derived from the spheroid defined by the SRID.
...
0
votes
2answers
1k views
Does MariaDB support ST_Distance_Sphere?
Running mariadb, I'm getting
ERROR 1305 (42000): FUNCTION ST_Distance_Sphere does not exist
When I run a basic query like
SELECT ST_Distance_Sphere( POINT(0,0), POINT(0,1) );
However, I'm using ...
1
vote
2answers
295 views
MySQL GIS functions strange results from ST_Distance_Sphere
According to wikipedia latitude article the distance between latitude 0 and 1 degrees is about 110.574 km. I double checked at gps visualizer site for points lat 0, long 0 and lat 1, long 0 and it ...
2
votes
1answer
332 views
Faster spatial queries for 100M records with Postgres
I am currenty using Postgres/PostGIS (one single instance) to run some geo queries to catch points that fall within specific shapes along with some extra data from the relative columns.
However even ...
2
votes
3answers
449 views
GIS Point or discrete longitude and latitude columns?
I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
1
vote
1answer
26 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
1answer
461 views
What's stored in spatial_ref_sys?
Should I copy data in this table when migrating to the other server?
6
votes
1answer
209 views
SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?
PostGIS provides a function called ST_MakeLine(pt1,pt2). It's great for constructing lines. MySQL does the same with LineString(pt1,pt2). What do you call the SQL Server analog that takes two Points ...
5
votes
1answer
1k views
SQL Server Point Constructor or version of ST_Point(x,y)?
PostGIS provides two point-constructors
ST_MakePoint(x,y,[z,[m]])
ST_Point(x,y)or the compliant version that does not support 3DZ or 4D
MySQL supports Point(x,y)
In SQL Server, Is there anyway to ...
