A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Questions tagged [st-distance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
18 views

Postgres ST_Distance_Sphere Runtime Improvements

I need to select all entries from a table, that are within a distance from a point. Each entry is a point geometry (I set a gist index on geom). Current runtime is anywhere between 3-15 seconds, ...
1
vote
1answer
35 views

Select points within a buffer around a line

Working with PostgreSQL. I have a Table with streets (Linestrings), and a Table with accidents (Points). The streets are divided into sections. I want to find the accidents within 50m around a certain ...
1
vote
2answers
40 views

PostGIS calculate distance to every point in table

I would like to calculate distance from point made by putting coordinate manually to every point in table. I made that code: SELECT a.name FROM ne_110m_populated_places as a CROSS JOIN (SELECT ...
2
votes
1answer
56 views

PostGIS - Find the “median” distance between two group of geometries depending on polygon area

I want to evaluate land distribution and concentration regarding to road network. Specificly, I'm trying to calculate the median distance between a shape of roads (multilinestring) and a shape of ...
2
votes
0answers
41 views

How can I optimize this PostGIS query?

I am using this query in PostGIS to get the closests points around a trace. This query is taking a long time to run : 15 sec with only 800 records in the POIs table. The first query get a list of all ...
2
votes
0answers
38 views

Getting distance between point and line with specific values?

I have a table named "housenumbers" that contains points with addresses fields, and one of them is "street_name". And I also have another table named "streets", that contains lines that are the ...
2
votes
1answer
57 views

Find closest polygon from point and get its attributes?

Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual ...
0
votes
1answer
59 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, ...
2
votes
0answers
79 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
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
1answer
60 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 ...
2
votes
2answers
144 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 ...
0
votes
1answer
110 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....
1
vote
2answers
369 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
votes
1answer
393 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 ...
5
votes
0answers
154 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 ...
0
votes
2answers
306 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
56 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 ...
2
votes
1answer
79 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 ...
3
votes
0answers
125 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 ...
0
votes
1answer
156 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
553 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 ...
2
votes
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?
5
votes
1answer
130 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 ...
0
votes
1answer
88 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 ...
2
votes
1answer
435 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. ...
0
votes
0answers
110 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
vote
0answers
236 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 ...
9
votes
1answer
216 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
5k 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 ...
1
vote
0answers
176 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 <#>...
3
votes
1answer
447 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....
1
vote
2answers
111 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....
3
votes
1answer
327 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 ...
5
votes
2answers
274 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.
1
vote
2answers
4k 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....
3
votes
1answer
622 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
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,...
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 ...