Questions tagged [st-distance]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
17 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
2answers
75 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 ...
7
votes
1answer
100 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 ...
1
vote
2answers
259 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 ...
0
votes
1answer
60 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 ...
1
vote
1answer
106 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 ...
2
votes
1answer
105 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 ...
1
vote
1answer
154 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
133 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
49 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
157 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
52 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
39 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
212 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 ...
3
votes
1answer
88 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
90 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
27 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
102 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
338 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
130 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
650 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
682 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
203 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
458 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
65 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
111 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 ...
4
votes
0answers
191 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
195 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
962 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 ...
3
votes
2answers
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?
5
votes
1answer
159 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
98 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
600 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
126 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
302 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
243 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 ...
7
votes
2answers
7k 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
189 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
546 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
123 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
362 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
337 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
6k 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
740 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 ...
4
votes
2answers
3k 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,...
11
votes
2answers
4k 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 ...