Questions tagged [spatial]

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

Filter by
Sorted by
Tagged with
7
votes
2answers
129 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
281 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
281 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
135 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
208 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
989 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
481 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
224 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
36 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
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 ...
1
vote
1answer
411 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. ...
3
votes
2answers
3k 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
505 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
498 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
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 ...
1
vote
1answer
31 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
2k views

What's stored in spatial_ref_sys?

Should I copy data in this table when migrating to the other server?
6
votes
1answer
314 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
2answers
2k views

SQL geography::point null parameter error when parameter is not null?

I was getting this error: 'geography::Point' failed because parameter 1 is not allowed to be null. On this sql: SELECT [ID], geography::Point([lat], [long], 4326) AS [loc] FROM ( SELECT [ID], ...
6
votes
2answers
677 views

Rebuilding a spatial index with minimum downtime

We just upgraded from SQL Server 2008 to 2014. It went fairly well except for a problem we have on a spatial index. On this table we receive the error Cannot insert duplicate key row in object 'sys....
1
vote
1answer
61 views

Updating column with multiple sub-queries

I have created the following query which is used to calculate a total from the results of a spatial query: SELECT SUM(med_price) FROM (SELECT ST_INTERSECTION(a.geom, (SELECT ST_SETSRID(geom, ...
0
votes
1answer
177 views

Is it possible to update all spatial index extents in one query?

I work for a county government and probably 95% of the data in the GIS database (running on Microsoft SQL Server 2014) is within the county area. I've noticed that in some of the data the extent of ...
1
vote
1answer
118 views

Fetch rows which belongs to a certain geo location

I've a table (locations) which contains records as given below: id | lat | lng | radius_in_km ----------------------------------------- 1 | -18.17943 | 83.968051 | 5 2 | 44.5796 | ...
0
votes
2answers
3k views

mySQL changing output unit of ST_Distance function

I'm trying to get a result in nautical miles between two lon/lat saved in a point data type field in mySQL. However, the result seems to be not in any unit I'm familiar with (maybe degrees). How can I ...
2
votes
0answers
207 views

Table design pattern that can support partitioning, clustered columnstore index, and spatial data?

I am designing a spatial database schema for SQL Server 2016 with a table that is going to hold a large amount of rows (50+ billions, increasing by 50+ millions per day). Requirements: Spatial ...
2
votes
1answer
76 views

Spatial Index Compression

I am looking to turn page compression on on a spatial index. The instance is 2008 Enterprise. The index is pre-existing on a vendor supplied app. In searching through BOL, it states that compression ...
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: ...
1
vote
1answer
514 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"+ ",...
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 ...
0
votes
1answer
418 views

Storing a 3D point array representing a flight path?

I need to store an array of 3D points in a column: latitude, longitude, altitude What datatype would be required for this? Also, if I wanted to extend this in the future to include time (ie an array ...
2
votes
1answer
471 views

Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?)

I have the following table: CREATE TABLE MY_DATA ( MY_DATA_ID NUMBER(38,0) PRIMARY KEY, GEOM SDO_GEOMETRY ); I've set its SDO metadata and created a spatial index: INSERT INTO ...
1
vote
0answers
176 views

Partition of tables with constraint exclusion doesn´t optimize queries

Over an existing table in PostgreSQL (version 9.0.2) I created nine others which inherited the former with check constraints. I turned on the constratint_exclusion variable and runned some queries, ...
1
vote
1answer
217 views

COM error on UnionAggregate

Has anyone ever come across a COMException while working with geography data in SQL Server? I didn't notice the error until I ran .Reduce() on the original zip code data. It may have happened before ...
1
vote
3answers
3k views

Optimising latitude and longitude selection query

I have table as follows the schema below. CREATE TABLE `gdata` ( `alarmTypeID` tinyint(4) NOT NULL DEFAULT '0', `fleetID` smallint(11) NOT NULL, `fleetGroupID` smallint(11) DEFAULT NULL, `...
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 ...
0
votes
1answer
245 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 ...
2
votes
2answers
685 views

After ID is updated, get a value from another table

I have a roads table: +---------+---------+ | ROAD_ID | MEASURE | +---------+---------+ | 1 | 1000 | | 2 | 100 | | 3 | 500 | | 4 | 9000 | +---------+---------+ ...
5
votes
1answer
91 views

Store both geometry and geography or store one and calculate the other?

I am just starting out designing my first GIS database, using PostGIS / PostgreSQL, and I have a question regarding geometry and geography, specifically whether it is more efficient to store geometry (...
3
votes
1answer
66 views

What is the proper type for columns holding GeoJSON?

What data type do you prefer for columns holding GeoJSON information, in the major RDBMS families (i.e. Oracle DB, PostgreSQL, MySQL, SQL Server, SQLite, DB2..? In general, do you prefer / is better ...
0
votes
3answers
785 views

MySQL database for storing city bounding boxes and names

I need to design a mysql database which contains a table of cities which supports only a single query - given a point, return the city (or cities) which it falls in. I have the bounding box for each ...
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 ...
0
votes
2answers
526 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),...
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
9
votes
4answers
579 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, ...
2
votes
0answers
40 views

Spatial index on raster tiles not used

I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat,...
1
vote
1answer
58 views

DB choice for a geodata application

I'm writing a client-server application where geotagged photos are main content. Backend is based on ASP.NET Core tech stack. Backend should be able to search photo in area (by radius), search photo ...
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)') ); ...
5
votes
3answers
459 views

Versioning: Is this technology used in DBMS other than spatial?

ESRI's spatial database management systems, called geodatabases (more), use a technology called versioning. A version represents a snapshot in time of the entire geodatabase and contains all the ...