From 398467b2f442cbe2c41c399f0871b94a34339033 Mon Sep 17 00:00:00 2001 From: gemo1011 Date: Wed, 13 Jun 2018 13:22:37 +0200 Subject: [PATCH] using ST_ClosestPoint and a subquery --- lib/PlaceLookup.php | 35 +++++++++++++++++------------------ 1 file changed, 17 insertions(+), 18 deletions(-) diff --git a/lib/PlaceLookup.php b/lib/PlaceLookup.php index 93ffd6a0..ca7a48cf 100644 --- a/lib/PlaceLookup.php +++ b/lib/PlaceLookup.php @@ -536,28 +536,27 @@ class PlaceLookup if (CONST_Search_AreaPolygons) { // Get the bounding box and outline polygon - $sSQL = 'select place_id,0 as numfeatures,st_area(geometry) as area,'; - if ($fLonReverse != null && $fLatReverse != null) { - $sSQL .= ' CASE WHEN (class = \'highway\') AND (ST_GeometryType(geometry) = \'ST_LineString\') THEN'; - $sSQL .= ' ST_Y(ST_LineInterpolatePoint(geometry,'; - $sSQL .= ' ST_LineLocatePoint(geometry, ST_SetSRID(ST_Point('.$fLatReverse.','.$fLonReverse.'),4326))))'; - $sSQL .= ' ELSE ST_Y(centroid) '; - $sSQL .= ' END as centrelat, '; - $sSQL .= ' CASE WHEN (class = \'highway\') AND (ST_GeometryType(geometry) = \'ST_LineString\') THEN'; - $sSQL .= ' ST_X(ST_LineInterpolatePoint(geometry,'; - $sSQL .= ' ST_LineLocatePoint(geometry, ST_SetSRID(ST_Point('.$fLatReverse.','.$fLonReverse.'),4326))))'; - $sSQL .= ' ELSE ST_X(centroid) '; - $sSQL .= ' END as centrelon, '; - } else { - $sSQL .= ' ST_Y(centroid) as centrelat, ST_X(centroid) as centrelon,'; - } - $sSQL .= ' ST_YMin(geometry) as minlat,ST_YMax(geometry) as maxlat,'; - $sSQL .= ' ST_XMin(geometry) as minlon,ST_XMax(geometry) as maxlon'; + $sSQL = 'select place_id,0 as numfeatures,st_area(geometry) as area,'; + if ($fLonReverse != null && $fLatReverse != null) { + $sSQL .= ' CASE WHEN (class = \'highway\') AND (ST_GeometryType(geometry) = \'ST_LineString\') THEN'; + $sSQL .= ' ST_Y(closest_point)'; + $sSQL .= ' ELSE ST_Y(centroid) '; + $sSQL .= ' END as centrelat, '; + $sSQL .= ' CASE WHEN (class = \'highway\') AND (ST_GeometryType(geometry) = \'ST_LineString\') THEN'; + $sSQL .= ' ST_X(closest_point)'; + $sSQL .= ' ELSE ST_X(centroid) '; + $sSQL .= ' END as centrelon, '; + } else { + $sSQL .= ' ST_Y(centroid) as centrelat, ST_X(centroid) as centrelon,'; + } + $sSQL .= ' ST_YMin(geometry) as minlat,ST_YMax(geometry) as maxlat,'; + $sSQL .= ' ST_XMin(geometry) as minlon,ST_XMax(geometry) as maxlon'; if ($this->bIncludePolygonAsGeoJSON) $sSQL .= ',ST_AsGeoJSON(geometry) as asgeojson'; if ($this->bIncludePolygonAsKML) $sSQL .= ',ST_AsKML(geometry) as askml'; if ($this->bIncludePolygonAsSVG) $sSQL .= ',ST_AsSVG(geometry) as assvg'; if ($this->bIncludePolygonAsText || $this->bIncludePolygonAsPoints) $sSQL .= ',ST_AsText(geometry) as astext'; - $sFrom = ' from placex where place_id = '.$iPlaceID; + $sFrom = ' from (SELECT * , ST_ClosestPoint(geometry, ST_SetSRID(ST_Point('.$fLatReverse.','.$fLonReverse.'),4326)) AS closest_point'; + $sFrom .= ' from placex where place_id = '.$iPlaceID.') as plx'; if ($this->fPolygonSimplificationThreshold > 0) { $sSQL .= ' from (select place_id,centroid,ST_SimplifyPreserveTopology(geometry,'.$this->fPolygonSimplificationThreshold.') as geometry'.$sFrom.') as plx'; } else { -- 2.45.1