Questions tagged [st-distance]
The st-distance tag has no usage guidance.
63
questions
1
vote
1
answer
53
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 ...
- 93
0
votes
1
answer
84
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 ...
- 1
0
votes
0
answers
26
views
Use case for calculating the geometric distance between points in two separate GIS layers
I am trying to calculate the distance between each transformer in one view layer and its respective upline substation (one per transformer) in another view layer. The upline substation is listed in ...
- 19
1
vote
0
answers
73
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....
6
votes
1
answer
369
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 ...
- 161
2
votes
1
answer
85
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,
...
- 131
8
votes
2
answers
260
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 ...
- 931
1
vote
0
answers
167
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, ...
- 131
0
votes
1
answer
101
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 ...
- 1
1
vote
1
answer
136
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) ...
- 639
1
vote
0
answers
290
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), ...
- 45
1
vote
0
answers
120
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 ...
- 11
3
votes
1
answer
339
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 ...
- 1,542
1
vote
0
answers
269
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 ...
- 39
0
votes
2
answers
159
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 ...
- 1
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 ...
- 131
1
vote
1
answer
919
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 ...
- 113
0
votes
0
answers
28
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 ...
1
vote
2
answers
373
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 ...
- 47
8
votes
1
answer
197
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 ...
- 183
1
vote
2
answers
4k
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 ...
- 13
0
votes
1
answer
493
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 ...
- 33
1
vote
1
answer
2k
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 ...
- 11
2
votes
1
answer
538
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 ...
- 55
1
vote
1
answer
602
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, ...
- 255
2
votes
1
answer
998
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
2
answers
625
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 ...
- 471
2
votes
1
answer
258
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 ...
- 23
2
votes
0
answers
100
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 ...
- 141
2
votes
0
answers
98
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 ...
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 ...
- 1,206
2
votes
1
answer
233
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, ...
- 617
2
votes
0
answers
220
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) ...
- 121
1
vote
0
answers
31
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" ...
- 617
1
vote
1
answer
450
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
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 ...
- 597
0
votes
1
answer
217
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....
- 111
1
vote
2
answers
1k
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 ...
- 597
-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
...
6
votes
0
answers
378
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 ...
- 331
0
votes
2
answers
884
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 ...
- 111
0
votes
1
answer
115
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 ...
- 1
2
votes
1
answer
220
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 ...
- 67
5
votes
0
answers
323
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 ...
- 151
0
votes
1
answer
464
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 ...
- 585
0
votes
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 ...
- 415
4
votes
2
answers
3k
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
1
answer
292
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 ...
- 231
0
votes
1
answer
118
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 ...
- 1
2
votes
1
answer
824
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.
...
- 161