Questions tagged [st-distance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
46 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) ...
user avatar
1 vote
0 answers
87 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), ...
user avatar
  • 45
1 vote
0 answers
38 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 ...
user avatar
3 votes
1 answer
112 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 ...
user avatar
  • 836
1 vote
0 answers
71 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 ...
user avatar
0 votes
1 answer
59 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 ...
user avatar
4 votes
2 answers
446 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 ...
user avatar
1 vote
1 answer
207 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 ...
user avatar
  • 113
0 votes
0 answers
26 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 ...
user avatar
1 vote
2 answers
218 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 ...
user avatar
8 votes
1 answer
134 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 ...
user avatar
  • 183
1 vote
2 answers
1k 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 ...
user avatar
0 votes
1 answer
219 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 ...
user avatar
  • 33
1 vote
1 answer
918 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 ...
user avatar
  • 11
2 votes
1 answer
310 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 ...
user avatar
1 vote
1 answer
301 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, ...
user avatar
  • 235
2 votes
1 answer
540 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 ...
user avatar
1 vote
2 answers
127 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 ...
user avatar
  • 461
2 votes
1 answer
215 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 ...
user avatar
2 votes
0 answers
81 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 ...
user avatar
  • 141
2 votes
0 answers
40 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 ...
user avatar
2 votes
1 answer
566 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 ...
user avatar
2 votes
1 answer
120 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, ...
user avatar
  • 565
2 votes
0 answers
159 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) ...
user avatar
  • 121
1 vote
0 answers
30 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" ...
user avatar
  • 565
1 vote
1 answer
210 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 ...
user avatar
2 votes
2 answers
649 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 ...
user avatar
  • 577
0 votes
1 answer
143 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....
user avatar
  • 111
1 vote
2 answers
980 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 ...
user avatar
  • 577
-1 votes
1 answer
974 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 ...
user avatar
6 votes
0 answers
278 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 ...
user avatar
  • 331
0 votes
2 answers
638 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 ...
user avatar
  • 111
0 votes
1 answer
84 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 ...
user avatar
2 votes
1 answer
152 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 ...
user avatar
5 votes
0 answers
241 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 ...
user avatar
  • 151
0 votes
1 answer
260 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 ...
user avatar
  • 555
0 votes
1 answer
1k 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 ...
user avatar
  • 405
4 votes
2 answers
2k 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?
user avatar
5 votes
1 answer
205 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 ...
user avatar
0 votes
1 answer
103 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 ...
user avatar
  • 1
2 votes
1 answer
704 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. ...
user avatar
0 votes
0 answers
130 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"....
user avatar
1 vote
0 answers
450 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 ...
user avatar
  • 111
9 votes
1 answer
247 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 ...
user avatar
  • 191
9 votes
2 answers
10k 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 ...
user avatar
  • 2,976
1 vote
0 answers
201 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 <#>...
user avatar
4 votes
1 answer
629 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....
user avatar
1 vote
2 answers
127 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....
user avatar
3 votes
1 answer
407 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 ...
user avatar
  • 85
5 votes
2 answers
404 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.
user avatar
  • 6,641