Hot answers tagged

20

I investigated exactly this question 20 years ago when designing a desktop GIS. We needed to find point-to-point distances interactively; our target was to do the computations in less than 1/2 second for thousands of points. Testing (on a 25 MHz 486 PC!) showed that we could compute all the distances, exactly as you describe (with the simple obvious ...


13

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") ...


10

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 ...


8

It seems like if you have a lot more customers than you do stores, then it might be more efficient to create a layer of voronoi polygons for the stores, then do a spatial join of customers against the store polygons.


8

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 ...


7

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

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)


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) ...


6

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=...


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

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, ...


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

From http://www.bostongis.com/?content_name=postgis_nearest_neighbor: If you needed to get the nearest neighbor for all records in a table, but you only need the first nearest neighbor for each, then you can use PostgreSQL's distinctive DISTINCT ON syntax. Which would look something like this: SELECT DISTINCT ON(g1.gid) g1.gid As gref_gid, g1....


5

Discussions about some basic nearest neighbor solutions can be found here: http://www.bostongis.com/?content_name=postgis_nearest_neighbor#120 /Nicklas


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

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

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 ( ...


4

likewise: select A.ID as CUST_ID, (select B.ID from B order by st_distance(A.geom,B.geom) limit 1) as STORE_ID from A


4

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 ...


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

This is typically described as contiguity based spatial weights, and is the most common type of spatial weights matrix used in spatial-lattice based regressions in the social sciences. Contiguity can typically be defined by either sharing an edge of the boundary (Rook), or touching somewhere at the edge of the boundary (Queen). The weights file is ...


4

I think you want to exclude the intersection of the buffer in the where clause. WITH subq AS ( SELECT p.id, p.name, unnest(ARRAY(SELECT q.name FROM w_point q WHERE p.id != q.id AND NOT ST_Intersects(q.geom, ST_Buffer(p.geom, 0.1)) ORDER BY ST_Buffer(p.geom, 0.1) <#> q.geom LIMIT 5) ) as name FROM w_point p ) SELECT ...


4

You should look at the output. In the toolbox window click on the results tab at the bottom (and if necessary, uncollapse the Average Nearest Neighbor entry). The NNI ratio, p value, expected and observed are all reported. You need to interpret the actual statistic and not rely in ESRI's GUI interpretation. A random or uniform distribution would be near ...


3

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 ...


3

I have a solution. Not necessarily pretty but it works on my test dataset and is actually fairly easy. First up, this only works if your "houses" and "office" categorisation is numerical because you can't calculate a mode (what you want) from non-numerical data in ArcGIS. That should be easy enough to arrange so I'll leave it as an exercise for the reader. ...


3

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


3

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::...


3

Digital elevation models and TIN algorithms (M. van Kreveld), section 1.6 may help.


3

Hallo There is some things do consider to make things move faster, and some things that might be possible in the future. First, you mentioned that you are considering using a buffer to find polygons in some minimum range to avoid calculating all combinations. As discussed in another link from Boston gis the right way to do that in PostGIS is using ...



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