Nearest neighbourhood queries with postgis

We had a task, which i believe would have taken days with ArcMap Editor. But postgis made it easier with updating a records and finding the points surrounding point from another table.

Here is a sample query.

Description: Find me exit locations, when two exits are within 150m from each other and update the table with shared infromation from each others.

UPDATE new_non_is_exits --new_is_exits_1 --rats
SET
int_id_rte_no=IS_EXITS1.id_rte_no,
int_road_name=IS_EXITS1.road_name,
int_mp_int_rte_name=IS_EXITS1.mp_int_rte_name,
int_routeid=IS_EXITS1.routeid,
int_id_prefix=IS_EXITS1.id_prefix,
int_mp_suffix=IS_EXITS1.mp_suffix
--of1 = tmp.of1,
-- sumof1 = tmp.sumof1
FROM (SELECT a.county, a.ogc_fid, a.id_rte_no, a.road_name,a.mp_int_rte_name, a.routeid, a.id_prefix, a.mp_suffix
--b.county , b.ogc_fid, b.id_rte_no, b.road_name,b.mp_int_rte_name, b.routeid, b.id_prefix, b.mp_suffix

FROM
--SELECT id, AVG(o) AS of1, SUM(o) AS sumof1
-- FROM rats,
-- offset1
-- GROUP BY id)
(SELECT ogc_fid,
county,
road_name,
id_rte_no,
mp_int_rte_name,
routeid,
id_prefix,
mp_suffix,
ST_Collect(wkb_geometry) AS geom
FROM New_IS_Exits_1
GROUP BY
ogc_fid,
county,
road_name,
id_rte_no,
mp_int_rte_name,
routeid,
id_prefix,
mp_suffix
) AS a,
(SELECT ogc_fid,
county,
road_name,
id_rte_no,
mp_int_rte_name,
routeid,
id_prefix,
mp_suffix,
ST_Collect(wkb_geometry) AS geom
FROM new_non_is_exits
GROUP BY
ogc_fid,
county,
road_name,
id_rte_no,
mp_int_rte_name,
routeid,
id_prefix,
mp_suffix
) AS b
WHERE ST_Distance(a.geom, b.geom) < 150 AND a.mp_int_rte_name = b.mp_int_rte_name AND a.county = b.county)
AS IS_EXITS1

Reproject the envelopes with ESRI JS API

A Reprojection on ESRI JS API can be done in two ways; I have used the project() method from Geometry Object.

This method has little bit of overhead, since it makes a graphic object and then gives us a four corners back, but it works.


var gsvc = new esri.tasks.GeometryService("http://sampleserver1a.arcgisonline.com/arcgis/rest/services/Geometry/GeometryServer");

getImageUrl: function(extent, width, height, callback) {

var symbol = new esri.symbol.SimpleMarkerSymbol();
var sfs = new esri.symbol.SimpleFillSymbol(esri.symbol.SimpleFillSymbol.STYLE_NONE, new esri.symbol.SimpleLineSymbol(esri.symbol.SimpleLineSymbol.STYLE_DASHDOT,
new dojo.Color([255,0,0]), 2),new dojo.Color([255,255,0,0.25]));
var extent = new esri.geometry.Extent(184594.889984, 27695.579988, 570505.380020, 231505.920012, new esri.SpatialReference({ wkid:26985 }));
//xmin:184594.889984,ymin:27695.579988,xmax:570505.380020,ymax:231505.920012,spatialReference:{wkid:26985}
var graphic = new esri.Graphic(extent, sfs);
var outSR = new esri.SpatialReference({ wkid: 4326});

var test = gsvc.project([ graphic ], outSR, function(features) {
pt = features[0].geometry;
});
//dojo.byId("latlong").innerHTML = " xmin = " + pt.xmin + "
 ymin = " + pt.ymin + "
 xmax = " + pt.xmax + "
 ymax = " + pt.ymax;


var params = {
action:"GetMapTile",
Layers:"W,T",
format:"png",
Opacity:"100,55",


//changing values
Corner1:extent.ymin + "|" + extent.xmin,
Corner2:extent.ymax + "|" + extent.xmax,
width: width,
height: height
};

callback("http://wmsserver/wms11?" + dojo.objectToQuery(params));
}