38

You are nearly there. There is a little trick which is to use Postgres's distinct operator, which will return the first match of each combination -- as you are ordering by ST_Distance, effectively it will return the closest point from each senal to each port. SELECT DISTINCT ON (senal.id) senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY") ...


16

I have reproduced your example with shapefiles. You can use Shapely and Fiona to solve your problem. 1) Your problem (with a shapely Point): 2) starting with an arbitrary line (with an adequate length): from shapely.geometry import Point, LineString line = LineString([(point.x,point.y),(final_pt.x,final_pt.y)]) 3) using shapely.affinity.rotate to ...


13

There are several ways you can tackle this in R, including spDists in sp and gDistance in rgeos. An efficient way, that is expandable to multiple kNN ID's and distances, is to use spdep. require(spdep) data(meuse) coordinates(meuse) = ~x+y meuse <- meuse[1:10,] meuse@data$IDS <- 1:10 # Neighbor row indices and add neighbor attribute ID's ( ...


12

This can be done with a LATERAL JOIN in PostgreSQL 9.3+: SELECT signs.id, closest_port.id, closest_port.dist FROM traffic_signs CROSS JOIN LATERAL (SELECT id, ST_Distance(ports.geom, signs.geom) as dist FROM ports ORDER BY signs.geom <-> ports.geom LIMIT 1) AS closest_port


10

a and b are alias table names to the same table. This is effectively a T1 CROSS JOIN T2 in DB-speak. This allows a self-join to say "how close one part is to another" in a single table. SELECT a.hgt AS a_hgt, b.hgt AS b_hgt, ST_Distance(a.the_geom, b.the_geom) AS distance_between_a_and_b FROM public."TestArea" AS a, public."TestArea" AS b WHERE ...


10

The aproach with cross-join doesn't use indexes and requires a lot of memory. So you basically have two choices. Pre 9.3 you'd use a correlated subquery. 9.3+ you can use a LATERAL JOIN. KNN GIST with a Lateral twist Coming soon to a database near you (exact queries to follow soon)


10

No need for JOIN LATERAL (or do you really just want to use it?); an UPDATE will pass each processing row to the following query, which is the same concept as using a JOIN LATERAL.[*]Try UPDATE gps SET stop_id = ( SELECT stops.stop_id FROM stops ORDER BY gps.geom <-> stops.geom LIMIT 1 ); You are a very experienced PostGIS user; still, let me ...


9

Try running the sp_help_spatial_geography_index stored procedure to get details on how your spatial index is being used. You should be able to use something like: declare @ms_at geography = 'POINT (-95.66 30.04)' set @ms_at = @ms_at.STBuffer(1000).STAsText() exec sp_help_spatial_geography_index 'lidar', 'SPATIAL_lidar', 0, @ms_at; Post the results in ...


7

There's a big "Nearest Neighbor" section on the BostonGIS page. EDIT: How about CREATE TABLE mytable_withinRange AS SELECT a.hgt AS a_hgt, b.hgt AS b_hgt FROM public."lon_TestArea" AS a, public."lon_TestArea" AS b WHERE ST_DWithin(a.the_geom, b.the_geom, 400) Concerning the CASE statement: SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 ...


7

A "very fast" query in the database against a moderately sized table will take about 15ms. Your test queries are taking 100 times less than that. Why do you think you have a speed problem? I'm pleased everything is as fast as all that, frankly. Yes, doing a proper geodetic distance calculation will take more time than a flat distance calculation on ...


7

You can check the source code for the Nearest Neighbour Analysis tool from GitHub. More specifically, the following lines of code which shows how the different parameters are calculated: do = float(sumDist) / count de = float(0.5 / math.sqrt(count / A)) d = float(do / de) SE = float(0.26136 / math.sqrt(( count ** 2) / A)) zscore = float((do - de) / SE) ...


7

This is essentially a duplicate question of multiple others, with the sole difference being a table self-join. However, all queries currently present in this post have delicate CRS misunderstandings, at least when it comes to distances: the main problem here is the threshold given to ST_DWithin; the units of that value are CRS dependent, thus, as the data ...


6

OK, I finally figure out a way to hack it that not only works around the dateline issue, but is also faster. CREATE OR REPLACE FUNCTION nearest_grid_point(point geography(Point)) RETURNS integer AS $BODY$ SELECT pointid FROM ( -- The normal case SELECT pointid, location FROM grid WHERE ST_DWithin($1::geometry, ...


6

If you don't want to compute the distances between all the point combinations, you can use a spatial index on one of the table : SELECT A.id , B.myValue, MIN(Distance(A.Geometry, B.Geometry)) AS distance FROM tableOne AS A, tableTwo AS B WHERE A.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'A' AND search_frame = ...


6

Would you be able to add the result of putting "EXPLAIN ANALYZE" before the query in your question? Then I can update my answer with suggestions. Of course, you will need GIST indexes on your table's geometry fields and vacuum analyze first. CREATE INDEX ON road USING gist (geom); CREATE INDEX ON point USING gist (geom); VACUUM ANALYZE road; VACUUM ANALYZE ...


5

To route along a road network requires more than simple linear referencing, so I'm afraid this is not a trivial task without some sort of routing add-on such as Network Analyst. Whether you have Network Analyst will depend on your licence. If you don't have Network Analyst you have three options as I see it. The first is to implement an A* algorithm in ...


5

Quantum GIS has excellent support for PostGIS (which I guess you can use at home since it's free software), so if you are familiar with it, you could script this procedure using SQL with something like this: UPDATE poly_layer p SET neighbors_class = ( SELECT class FROM ( SELECT class, count(0) FROM poly_layer n WHERE ...


5

What you are looking for is Nearest Neighbor Query. Look at the following links, I think you will find what you are looking for. Nearest Neighbor Query Nearest Neighbors The nearest neighbor optimization in SQL Server Denali


5

I have just tested this SQL and it works: SELECT g1.OGC_FID As id1, g2.OGC_FID As id2, MIN(ST_Distance(g1.GEOMETRY,g2.GEOMETRY)) AS DIST FROM table_01 As g1, table_02 As g2 WHERE g1.OGC_FID <> g2.OGC_FID AND ST_Contains(ST_Expand(g1.geometry,50),g2.geometry) GROUP BY id1 ORDER BY id1 As you can read here "The naive way to carry out a nearest ...


5

Don't use the distance operation unless you actually need the distance. You can use the ST_DWithin to get geometries within a certain distance. Right now I don't have a PostGres database to test and give you a SQL query for your data, but have a look at the sample query given on the documentation page


5

Just extract the upper triangular part of points_matrix and use the column sums as a criterion to remove the points: points_matrix <- gWithinDistance(points, dist = 20, byid = TRUE) points_matrix[lower.tri(points_matrix, diag=TRUE)] <- NA points_matrix # 1 2 3 4 5 # 1 NA FALSE FALSE FALSE FALSE # 2 NA NA FALSE FALSE FALSE # 3 NA ...


5

You can do this with two UPDATE statements, one for the distance, and the second for the line ID, with a subquery to get the values from the line table. And use the ORDER BY ST_Distance(...) LIMIT 1 construct to get only the closest line. I have a cities point layer, and a hiways line layer. Each has a primary key column 'pk'. I added to the cities two ...


5

I did have the same the same research question as you. I wanted to calculate the distance from archaeological sites to rivers. I did have access to a hydrological layer by INEGI. I did have rivers as lines and as polygons. This is what I did. First I converted the polygons to lines using vector/ geometry tools/polygon to lines. Now I did have all my rivers ...


5

You're not getting any benefit from your spatial index, because you're storing your locations as geometry(Point, 4326) but querying them either as geography (Query 1) or as geometry(Point, 26986) (Query 2). Any of the following changes would fix this: Query 1: Switch your geometry column to type geography ALTER TABLE businesses ALTER COLUMN location ...


5

Result of clustering technique suggested by @Albert shown by colours of points in the picture below. Output will greatly depend on physical order of points in a feature class. At some stage it will result in "islands", that are grouped in a very disperse "cluster", e.g. red points in group "C" below. Note that points are labelled by their FID. Algorithm ...


4

Following R.K. suggestion, I have made 3 diferent rasters to test the NN resampling method in arcGIS and when passing from InRas resolution to a resolution that is 1/2 of it, the value of the new cell is allways given by the lower right input cell. On the left the different InRas files I've created (cell size1, 6x6), on the right the output of the ...


4

This uses Geography not Geometry (if data is Lat/Lng you data should be Geography Type not Geometry) "The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates." To Select the Top 5 Nearest Records from a lat/lng (-122.0 37.0) point you can use. SELECT TOP 5 geography::...


4

With SRS/Map projections, it's always a trade off. There really isn't one that is a good fit for all places of the world. Might as well assume that the earth is a sphere. Instead of looking for a SRS that fits the whole world, I think you're better of looking for distance calculation algorithms. An example is the Great Circle Distance which is based on ...


4

There is the spdep R package, which has a K-nearest neighbor algorithm implemented. I am not sure if its more efficient than what you have, but it might be worth a try. Here is an example script to show how you could calculate the values you wanted and put them into a data frame. # Load spdep package library(spdep) # Load example data # (replace this by ...


4

Edit: Your problem is that ST_MakePoint nad ST_MakeLine doesn't take WKT/EWKT format as input. Working example query : ( My test table is in SRID 4326 ) SELECT ST_Distance(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326)) as distance,p.fips FROM world p WHERE ST_DWithin(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326),10) ORDER BY ...


Only top voted, non community-wiki answers of a minimum length are eligible