Questions tagged [st-distance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
10
votes
2answers
3k views

ST_Distance doesn't use index for spatial query

I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here). The table is created ...
9
votes
1answer
209 views

Finding closest line given 2 points using PostGIS?

I have a table t that contains a column line_positions which is of type line. Given 2 points I want to find the closest line that is close enough (less than 10km) and that does not pass too close to a ...
5
votes
2answers
237 views

Investigating PostGIS functions to see what they are doing?

Let's say you have a function like ST_Distance and I want to know how it works. The docs don't say much at all, how do I go about debugging this or figuring out how it ticks.
5
votes
2answers
4k views

How to calculate distance from POINT to LINESTRING in R using `sf` library and get all POINT features from LINESTRING where distances were calculated?

I'm trying to calculate distances from a set of POINT features (1, 2, 3, 4, 5, 6 & 7) to a LINESTRING feature using sf library in R. I could calculate all the distances using the function ...
5
votes
1answer
104 views

ST_DistanceSphere and KNN discrepancy

I have a table containing thousands of data points around London. I'm trying to write a query to find the closest data points to a given latitude and longitude. I'm getting different results based ...
5
votes
0answers
127 views

PostGIS ST_Length returns 0 for geographies with high number of decimals

I'm having a weird issue when computing the distance between two geographies using ST_Length (PostgreSQL 10.4, PostGIS 2.4.4). Basically if the number of decimals is above six, ST_Length returns 0 in ...
3
votes
1answer
301 views

How reliable is Postgis's ST_Distance

I am very confused about the measurements of ST_Distance in PostGIS. If I measure along the equator I get longer measurements using the SPHEROID instead of the sphere, but only up to 179 degrees (and ...
3
votes
1answer
568 views

Neo4j distance vs PostGIS distance with spatial data

Does anyone know how the distance function of neo4j/cypher works exactly? When i compare the evaluated distances between neo4j and postgis, i always get a difference of about 0.1-0.5%. As postgis ...
3
votes
1answer
382 views

ST_DISTANCE return not same distance as QGIS

I am searching distance between point and nearest road (OSM data). I wrote next SQL request: SELECT osm_id, st_astext(geometry), ST_DISTANCE(geometry::geography, ST_SetSRID(ST_POINT(37.72308, 55....
3
votes
0answers
95 views

Most performant KNN method in postgis (<->, <<->>, st_distance)

My question is about the fastest way to order query results by approximate distance. I've seen conflicting information about this. My data are lat/lng points stored using WGS84 (SRID 4326). The ...
2
votes
1answer
74 views

ST_3DDistance error

I do not understand the error I get when I try to execute the following query: SELECT ST_3DDistance(location) FROM public.cycle_station; ERROR: function st_3ddistance(geometry) does not ...
2
votes
1answer
340 views

Assistance Speeding up a Query with ST_Distance_Sphere

I'm using PostGIS/Tiger for geocoding I am trying to find the first/nearest tract for a given long/lat, and while this query works great every time, it takes anywhere from 2-5 seconds to complete. ...
2
votes
2answers
2k views

How can I optimize this ST_Distance query?

I am using ST_Distance in postgis to get the closest point in a table to each polygon in another table. This query is taking a long time to run (2 hours with 18,000 records in the polygon table and 7,...
2
votes
2answers
74 views

PostGIS: Calculating the shortest distance between one point and MULTILINESTRING objects

I have a table with MULTILINESTRING objects and I want to build a query that outputs the shortest distances between every MULTILINESTRING object and a specific point (the same for all records) using ...
2
votes
0answers
65 views

Why ST_Distance give something different to 0?

I have this SQL: select ST_Distance(GeomFromText('POINT(-0.338767 39.55425)', 4326), geom) dis1, ST_Distance(GeomFromText('POINT(-0.338767 39.55425)', 4326), geom, 1) dis2, AsEWKT(geom) ...
1
vote
2answers
1k views

Distance between a point and polygon

What is the meaning of distance between a point and polygon while using ST_Distance? I'm aware that it means the shortest distance between them but what is here meant by shortest distance?
1
vote
2answers
3k views

Distance calculation using ST_Distance

I am trying to calculate the distance using the ST_Distance function in the postgres database. I am using following query: SELECT ST_Distance( ST_GeogFromText('SRID=4326;POINT(52.3551151038034 4....
1
vote
1answer
48 views

Table to Distance Between in PostGIS

I am a new user in PostGIS. I need to create a table with the ID_DIR of table A, ID_SQ of table B and have a new column called "Distance" that would be the distance between the ID_DIR and the ID_SQ in ...
1
vote
2answers
286 views

Calculating distance between a Point and a MultiPolygon PostGIS object

I'm working in a layer with MultiPolygons and I'm trying to calculate the shortest distance between a MultiPolygon and a point which is contained in that MultiPolygon, using Django and PostGIS. At ...
1
vote
0answers
26 views

Minimal Distance Between a geometry and a layer on PostGIS [duplicate]

I am working on PostGIS where I have a two tables. Let's call them A and B. A is coming from a multi polygons layer. B is coming from a multi points layer. I have created a new column "min_distance" ...
1
vote
0answers
199 views

ST_DistanceSphere does not use index [closed]

Here is the tables: -- 14 000 records CREATE TABLE public.storagepricingapp_storagelocation ( id integer NOT NULL DEFAULT nextval('storagepricingapp_storagelocation_id_seq'::regclass), location ...
1
vote
0answers
166 views

Point to Polygon nearest distance DS_distance is not using geography index & knn <-> or <#> does not give result in order [closed]

The below query gives result in wrong order. SELECT *, ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(-75.66,45.33), 4326 ) , geometry ) / 1609.34 AS distance FROM myTable ORDER BY geometry <#>...
1
vote
2answers
104 views

Geographically closest 2 airports between 2 cities

I have a database of all airports and need a query that finds the shortest distance between airports in 2 locations (cities for example). Here is my query. SELECT a.city, a.iata_faa, md....
0
votes
2answers
212 views

Wrong Calculated distance in PostGIS

I have downloaded Malaysia map from opensteetmap website and I have started to work on it in PostGIS. I need to find out the distance of some points to the nearest street over the map, so I used this ...
0
votes
1answer
143 views

Optimizing my script; taking too long; distance between many objects PostGIS

I have many many points and want to calculate the shortest distance from each point to the closest line of a table with many many lines. I think my underneath code is correct, I however never get an ...
0
votes
1answer
423 views

Joining tables based on distance using SQL Server

I need to show the three closest hospitals to every house. I have a table of properties and a table of hospitals. Both are in MSSQL2012, have a geography column that is indexed and are in the same ...
0
votes
1answer
44 views

Deciding whether to cast or Project when meters needed for ST_Distance?

I have a database and I am using geoms to build polygons around points. I need to do st_distance, st_area, st_overlaps, st_union, and st_instersects. also using st_x and st_y and st_project, so ...
0
votes
1answer
88 views

Find all regions (lat, long, radius) that contain a point

A Region has a latitude, longitude (GEOMETRY('POINT')) and a radius. Using postgis, how can I query all Regions where the distance from pointParam and region.point is less than or equal to region....
0
votes
1answer
69 views

Q: Issue finding the nearest street segment(s) based on an x,y coordinate and ordering by distance

I'm trying to pull the SNOW_ROUTE attribute from the nearest street segments based on an X,Y coordinate and order the results by nearest segment. I'm able to return the snow route information but the ...
0
votes
0answers
94 views

Finding list of points near current long/lat using PostGIS?

[UPDATE] What I have now is light years better than I did before. With the suggestion to look at ST_POINT, I restarted my googling and the query looks now like so: SELECT "ID" from "CityBottle"....
-1
votes
1answer
29 views

Defining distance between points in meters

I want to calculate the distance in meters between two points but I am getting an obscene result 5497980.47338251 Here is the query I made: SELECT ST_Distance(ST_SeTSRID(ST_MakePoint(6761331.47, ...
-1
votes
1answer
284 views

st_distance returns different answer than haversine formula

I have two coordinates stored as geometry in a table. They are stored in column named latlng. Now the coordinates are: 1 31.54696470032501, 74.3782182429078 2 31.548894670525055, 74.37833017871426 ...