[GIS] PostGIS ST_Within returns zero data

point-in-polygonpostgispostgresql

I am using PostgreSQL 9.3.3 & PostGIS 2.1.1 and I have a table Entries which has a column called "latlng" of geographic type (this column stores entry's latitude and longitude coordinates).

I am trying to find all entries that belong to a given polygon – like this:

entries = Entry.find_by_sql("
    SELECT *
    FROM Entries
    WHERE ST_Within(
        Entries.latlng::geometry,
        ST_GeomFromText(
            'POLYGON((
                #{swLat} #{swLng},
                #{swLat} #{neLng},
                #{neLat} #{neLng},
                #{neLat} #{swLng},
                #{swLat} #{swLng}))',
            4326)
    );")

Which translates into the following SQL statement:

SELECT *
FROM Entries
WHERE ST_Within(
    Entries.latlng::geometry,
    ST_GeomFromText(
        'POLYGON((
            45.61490708797995 -121.32521530382634,
            45.61490708797995 -121.32338469617367,
            45.615492910571655 -121.32338469617367,
            45.615492910571655 -121.32521530382634,
            45.61490708797995 -121.32521530382634))',
        4326)

Even though I am sure that my table has entries that have coordinates that belong to such polygon, I get zero results back. Any ideas on what could be causing this?

Best Answer

As you already have specified into your question, the coordinates must be expressed following the "latlng" order, therefore I suggest you to store the data in your table in the proper oder (the longitude first and the latitude second).

Using this logic, your second statement must be reformulated as below:

SELECT *
FROM Entries
WHERE ST_Within(
    Entries.latlng::geometry,
    ST_GeomFromText(
        'POLYGON((
            -121.32521530382634 45.61490708797995,
            -121.32338469617367 45.61490708797995, 
            -121.32338469617367 45.615492910571655,
            -121.32521530382634 45.615492910571655,
            -121.32521530382634 45.61490708797995))',
        4326)

Also, you must to assure yourself your "latlng" colum is really of geometry type (for example a point). If this is you case, then you simply may specify the column name

latlng

instead of 

Entries.latlng::geometry

But, if your "latlng" column looks like this:

POINT(-121.32 45.61)

then, you must use

ST_GeomFromText(latlng, 4326)
Related Question