Searching closest region from Google Maps coordinates

Question

Here is my problem. I'm mapping a region in Google Maps after that I'm saving the coordinates in PostGIS database as geographic polygon type.

ST_GeographyFromText('SRID=4326;POLYGON((..))')

The goal of my script is to find the closest region to a given point selected from Google Maps.

Here is my solution which is not working very well.

So what I'm doing is to break the region polygon and to find the closest point to my desire point. But the accuracy is not very precise. Some times when my point is closer to a given region it selects a region which is pretty far from it.

SELECT
        ST_Distance (
            ST_GeographyFromText('SRID=4326;POINT(" . $point->longitude . " " . $point->latitude . ")'),
            geom::geography
        ) AS distance
    FROM
        ST_DumpPoints (gis_coords::geometry) --gis_coords is the region column.
    ORDER BY
        distance ASC
    LIMIT 1
) AS distance

When I try to render my region column with QGIS and OpenLayers Plugin the result is pretty disturbing.

QGIS Settings

I'm setting the region layer CSR to WGS 84 (EPSG:4326) and for OpenLayers WGS 84/Pseudo Mercantor (EPSG: 3857)

Answer 1

I think you've got a couple things going wrong. First, ST_Distance() works fine with all types of geometries, not just points, so let's get rid of the ST_DumpPoints. Next, your map seems to demonstrate that you've got latitude and longitude mixed up somewhere (or you're ommitting a negative sign?). So to re-write your sql to select the region based upon the smallest distance, I would try something like this:

$sql = "SELECT regionName, ST_Distance(the_geom, ST_SetSRID(ST_MakePoint(" . $point->longitude . ", " . $point->latitude . "),4326)) as theDistance FROM gis_coords ORDER BY theDistance LIMIT 1;";