Skip to main content

Questions tagged [st-distance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
3 votes
0 answers
57 views

PostGIS: Why does <-> and ST_Distance give two different results?

I have a query where I want to get the closes row by coordinate distance like so: SELECT A.id, st_distance(coords, ST_SetSRID(ST_MakePoint(-9.15054,54.115341), 4326), true) AS dst, A.coords &...
bcsta's user avatar
  • 191
4 votes
2 answers
95 views

Calculating accurate distances using various ST_Distance options

I want to get the nearest neighbour information for a point, and I'm getting different results based on different geometry/geography types. Assuming I only care about the most accurate result, which ...
Ruslan's user avatar
  • 695
1 vote
1 answer
103 views

Use ST_Distance() to filter a maximum of distances

I've tried to select only the schools/kindergarten that are maximum 1000m distant from the train station. Here is my code SELECT gebaeude.name AS "Name", ST_Distance(geom,(SELECT geom FROM ...
Alm's user avatar
  • 83
1 vote
1 answer
65 views

How to Calculate Summary Statistics For Distance Bands in R [closed]

I currently have a shapefile with tourism data by district for the country of Jamaica, but I would like to try and also obtain for each district the average amount of tourism activity in districts ...
Matt's user avatar
  • 93
0 votes
1 answer
680 views

Using sf package for R to calculate distances for large dataset of location pairs

Working with data set of about 84k location pairs and trying to run in same manner as example code below. Getting results where distances are returned in an object of n x n dimension so when run on ...
lhilsbos's user avatar
1 vote
0 answers
117 views

ST_Length and ST_Distance not returning the correct results

Result I am getting back from both functions is 0.04036905073196378 --E Fletcher Ave and N 46th St 28.069239, -82.409880 --E Fletcher Ave and Lettuce Lake Pkwy 28.069303, -82.369511 --2.5 Miles or 4....
Felix Hernandez's user avatar
6 votes
1 answer
378 views

Documentation for <->

I found this question and am wondering, is it for PostgreSQL? Where’s the documentation for <->? Trying to find the fastest way to get the distance between a lat and long earth coordinates in ...
user433342's user avatar
2 votes
1 answer
120 views

PSQL can I speed up a distance and string matching calculation query

I have a query that checks a lat,lon pair with each coordinate column in a PostgreSQL table. Then gets the shortest distance like so: SELECT A.id, A.name, similarity(A.name, %s) AS sim, ...
bcsta's user avatar
  • 191
8 votes
2 answers
359 views

Calculate the distance between a single point and multiple polygons using PostGIS

I’m working with a very large dataset and I need to calculate the shortest distance between a single point and the closest edge from multiple polygons. I need to use PostGIS because such a task with a ...
Filipe Dias's user avatar
1 vote
0 answers
257 views

Add (bind/join) multiple st_distance columns to sf object in R

I measured multiple distances between 2 sf objects using st_distance and I need to add these multiple distance columns to one of the previous sf objects. However, I'm not sure which is the best way, ...
N.Varela's user avatar
  • 131
0 votes
1 answer
139 views

how to store a road with multiple routes?

I assume everybody knows google maps. When you get any direction to you destination there is always mutliple options. Let's say I want to store such a geometry in PostGIS? What is the best approach to ...
Radan's user avatar
  • 1
1 vote
1 answer
162 views

Invalid geometry point in ST_Distance query PostGIS

I have a problem that I can't get over .... I have a map on which there are several lines that represent some roads in the commune I click on the map and it gives me the position (latitude, longitude) ...
CoderCoder42's user avatar
1 vote
0 answers
343 views

Why ST_DistanceSphere returns 0? [closed]

Why when I use ST_DistanceSphere() for obtain the distance in meters between to points I get 0 as result? This is the query: SELECT ST_DistanceSphere(ST_MakePoint(54.1098335, 7.9320379), ...
Uderr's user avatar
  • 45
1 vote
0 answers
148 views

ST_Distance column empty in self join query

So I am working on an assignment with a database of GIS club members that includes the following columns: id, firstname, lastname, postalcode, and geom. I am querying to find which member is closest ...
Isabella's user avatar
3 votes
1 answer
490 views

Use of ST_Distance in QGIS SQL Statement

I am using QGIS 3.18. I have two shapefiles. A point layer representing tree positions ("tree_pt") and a building layer representing a building ("building") containing multiple ...
Oisin's user avatar
  • 1,846
1 vote
0 answers
390 views

Distance to forest edge in R

I have a raster layer consisting 41 forest polygons and would like to calculate the distance of forest pixels to the edge within each forest polygon. I tried the code below but the result looks like ...
Donald 's user avatar
0 votes
2 answers
178 views

Performing ST_Distance query in file geodatabase using ArcGIS Pro

I'm trying to create a view in ArcGIS Pro that joins attributes from two feature classes. The features need to be joined by an ID field and by distance. I want the features in the TABLE1_ERASE layer ...
GIS 816's user avatar
4 votes
2 answers
2k views

Calculating distance between a point and the nearest line, PostGIS

I have to add a column to my points table storing the distance (metres) between each point and the nearest line. Both are in the same projected coordinate system. How can I do it? I've tried this but ...
Luigi Falco's user avatar
1 vote
1 answer
1k views

Query the distance to the nearest location through a join table

I'm looking to select the distance from a given location to the nearest location in a joining table, and order by it. In my use case I have a listings table which is joined to locations table through ...
Dwight's user avatar
  • 113
0 votes
0 answers
29 views

Postgresql POSTGIS not using index field while having GIST index on geometry field [duplicate]

I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq Indexed ...
A l w a y s S u n n y's user avatar
1 vote
2 answers
471 views

Calculating perpendicular distance from centroid of polygon to multiple lines in QGIS

In QGIS 3.10.4, I have two shapefiles (polyline and polygon, layer name 'boundaries', 'lines' respectively), look like below: My goal is to calculate the perpendicular distance from the centroid of ...
orange's user avatar
  • 47
8 votes
1 answer
212 views

Why are my icosahedron triangle subdivisions not equilateral (PostGIS)?

I am creating a geodesic polyhedron using PostGIS. I am dividing the base icosahedron points (see Appendix A), into the first level of Class I subdivisions. The setup For simplicity let's take just ...
poshest's user avatar
  • 183
1 vote
2 answers
7k views

calculate st_distance between the point in km (m)

I try to calculate the distance between two geogprahical points, using st_distance. The points are stored in a data frame, but for simplicity, I take one pair of points to show the problem. Here are ...
marina_esp's user avatar
0 votes
1 answer
575 views

Using st_distance with apply functions and iterators

I'm working with a dataset of islands in which I need to calculate the distance between each island and the nearest landmass with an area greater than that island. I can do this successfully with a ...
Danple's user avatar
  • 33
1 vote
1 answer
3k views

How to calculate the distance of points to line in R?

I need the distance from each point of the object found to the transect line. 'id' is a research transect, and 'obj.id' is the object found. in this case, 1 object was found in transect A and 2 ...
mnf's user avatar
  • 11
2 votes
1 answer
612 views

Find all closest points to unique point in PostGIS

I have two tables valid_collisions (POINT) and intrct_pts (POINT) I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions. In essence I am ...
sTonystork's user avatar
1 vote
1 answer
732 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, ...
guyts's user avatar
  • 255
2 votes
1 answer
1k 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 ...
Peter_Mountain's user avatar
1 vote
2 answers
821 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 ...
Roman's user avatar
  • 471
2 votes
1 answer
287 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 ...
Rob Lucas's user avatar
2 votes
0 answers
106 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 ...
Ben's user avatar
  • 141
2 votes
0 answers
131 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 ...
santamariense's user avatar
3 votes
1 answer
1k 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 ...
Pitrako Junior's user avatar
2 votes
1 answer
347 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, ...
Basile's user avatar
  • 617
2 votes
0 answers
251 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) ...
santos82h's user avatar
  • 121
1 vote
0 answers
32 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" ...
Basile's user avatar
  • 617
1 vote
1 answer
508 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 ...
Marcelo Esteban Lagos Bustaman's user avatar
2 votes
2 answers
1k 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 ...
Capdi's user avatar
  • 617
0 votes
1 answer
230 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....
KKendall's user avatar
  • 111
1 vote
2 answers
2k 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 ...
Capdi's user avatar
  • 617
-1 votes
1 answer
2k 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 ...
Ehsan Bhatti's user avatar
6 votes
1 answer
466 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 ...
Jukurrpa's user avatar
  • 331
0 votes
2 answers
1k 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 ...
Moohebat's user avatar
  • 111
0 votes
1 answer
125 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 ...
JennayQ's user avatar
2 votes
1 answer
234 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 ...
hazard335's user avatar
5 votes
0 answers
368 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 ...
yeedle's user avatar
  • 151
0 votes
1 answer
524 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 ...
Zuenie's user avatar
  • 585
1 vote
1 answer
2k 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 ...
AK9's user avatar
  • 425
5 votes
2 answers
4k 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?
Souvik Haldar's user avatar
5 votes
1 answer
308 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 ...
Joaquim d'Souza's user avatar