Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
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 ...
15
votes
3answers
8k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
4
votes
1answer
547 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 ...
9
votes
2answers
4k views

Does MySQL store point datatypes as LAT LNG or LNG LAT?

I'm used to seeing location format as latitude followed by longitude, but using libraries, I believe I understand MySQL to store it as POINT(LNG LAT), the reverse order. Is my library in the wrong, or ...
7
votes
3answers
1k views

What are object-relational databases, and why is this model necessary in spatial databases?

Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational. What are object-relational databases, and why is it necessary to use this model in spatial ...
23
votes
1answer
907 views

Are unfixable spatial index corruptions considered normal?

I have a spatial index for which DBCC CHECKDB reports corruptions: DBCC CHECKDB(MyDB) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS The spatial index, XML ...
8
votes
3answers
583 views

Cross join on a numbers table to get line vertices, is there a better way?

The Question: I have a spatial table (road lines), stored using ESRI's SDE.ST_GEOMETRY user-defined datatype in an Oracle 12c geodatabase. I want to list the line vertices so that I can ultimately ...
5
votes
2answers
2k views

Good layout of 3d point data for spatial queries in Postgres?

Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this: CREATE TYPE float3d AS ( x real, y real, z real); If I am ...
3
votes
1answer
2k views

Finding the nearest geo points across two tables?

I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M. Along with the lat/lon columns I have created a geo-indexes for both tables in this way: ...
2
votes
3answers
566 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 ...
0
votes
1answer
64 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 ...
20
votes
3answers
667 views

Large (>22 trillion items) geospatial dataset with rapid (<1s) read query performance

I'm in the process of designing a new system for a large geospatial data set that will require rapid read query performance. Therefore I want to see if anyone thinks it is possible or has experience/...
3
votes
1answer
1k 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 ...
5
votes
2answers
4k views

How can I speed-up my query on geo-location processes

I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query: SELECT *, point(45....
5
votes
4answers
213 views

Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

I have a road_vertices table: create table road_vertices ( road_id number, vertex_index number, x number, y number ); insert into road_vertices values ('100',1,0,5); insert into road_vertices values ...
9
votes
3answers
3k views

How does Yelp efficiently calculate distance in the database?

For example, say I have a table: Business(BusinessID, Lattitude, Longitude) All are indexed of course. Also there are 1 million records Say I want to find businesses closest to 106,5, for example, ...
8
votes
1answer
18k views

Decimal or Point Data Type for storing Geo location data in MySQL

I want to store Geo location for each Address in my MySQL(Innodb) "tblAddress" table and I think I have 2 Options: 1) 2 columns for Each Address: Latitude DECIMAL(10, 8), Longitude DECIMAL(11, 8) 2)...
2
votes
1answer
4k views

MySQL Syntax - Searching points within polygons using GeomFromText

I am trying to select points within polygons using the GIS/spatial extensions for MySQL. I'm fairly new to this, so bear with me if this is an elementary question. I have points defined as text ...
2
votes
1answer
389 views

MySQL equivalent of a spatial ST_Union aggregate function?

I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function UnionAggregate, or the ...
13
votes
2answers
2k views

Alternative to MakeValid() for spatial data in SQL Server 2016

I have a very large table of geography LINESTRING data that I'm moving from Oracle to SQL Server. There are a number of evaluations that are executed against this data in Oracle, and they will need ...
9
votes
4answers
580 views

Problem getting zips in radius via MySQL

I have a table of zip codes which includes the center lat, lng for each zip code. I use it to get a list of zip codes within a given mile radius from any arbitrary point. It just occurred to me that, ...
7
votes
1answer
2k views

Some SQL Server geospatial queries take much, much longer than others

We have a simple SQL Server table with geospatial data that looks like this: CREATE TABLE [dbo].[Factors]( [Id] [int] IDENTITY(1,1) NOT NULL, [StateCode] [nvarchar](2) NOT NULL, [...
6
votes
2answers
605 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM `...
6
votes
1answer
315 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 ...
6
votes
1answer
2k 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 ...
4
votes
1answer
347 views

Non-overlapping rectangles constrained to a boundary

I am trying to model placement of parts on a circuit board. Without any meaningful constraints, my basic schema looks like this: create table part ( part_id bigserial primary key, name text ...
2
votes
1answer
217 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 ...
2
votes
2answers
12k views

Cannot get geometry object from data you send to the GEOMETRY field

The following simple test returns an error CREATE TABLE track ( position point NOT NULL ); INSERT INTO track VALUES ( position=ST_PointFromText('POINT(-45.62390335574153 -3.9551761173743847)') ); ...
1
vote
2answers
516 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 ...
1
vote
1answer
418 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. ...
1
vote
1answer
515 views

How do I dump spatial types, like point, with their SRIDs in MySQL?

I am testing the point data type on Mysq 5.7 below is my java insert query. String insertQuery1 ="INSERT INTO gtest3 SET " + "gDateTime='2017-07-16 01:00:00'"+ ",vehicleID=1"+ ",...
1
vote
1answer
363 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
2answers
529 views

Converting spatial data from Oracle to MySQL

This is a followup to this question: Problem getting zips in raius via MySQL I have some spatial stuff in an Oracle table. MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),...
0
votes
1answer
247 views

Can PostGIS use spatial indexes on foreign tables?

I'm attempting to perform a query on some foreign spatial tables which is taking several orders of magnitude longer than running on the normal spatial table because the index is not used. Indexes ...