Yup, it looks like that is the behaviour from JTS and GEOS. The problem is that your LINESTRING is invalid. If you have PostGIS 2.0, you can use ST_MakeValid(geometry) to fix the LINESTRING to a POINT.
This query verifies your bug, and uses ST_MakeValid as a workaround.
WITH data AS (SELECT 'POLYGON((150 280, 99 215, 190 210, 150 280))'::geometry AS poly,
'POINT(170 240)'::geometry AS pt)
SELECT ST_Intersects(poly, pt) AS intersects_poly_pt,
ST_Intersects(poly, ST_MakeLine(pt, pt)) AS intersects_poly_line,
ST_IsValid(ST_MakeLine(pt, pt)) AS isvalid_line,
ST_AsText(ST_MakeValid(ST_MakeLine(pt, pt))) AS valid_geom,
ST_Intersects(poly, ST_MakeValid(ST_MakeLine(pt, pt))) AS intersects_poly_valid_geom
FROM data;
with results (using psql's \x
option):
NOTICE: Too few points in geometry component at or near point 170 240
-[ RECORD 1 ]--------------+---------------
intersects_poly_pt | t
intersects_poly_line | f
isvalid_line | f
valid_geom | POINT(170 240)
intersects_poly_valid_geom | t
If you are using a previous version of PostGIS (pre 2.0), then you can cast from an invalid LINESTRING to a box2d, then back to a GEOMETRY. For a two-vertex LINESTRING with same coordinates, this turns into a POINT. Here is the PostGIS 1.5 version of the above:
WITH data AS (SELECT 'POLYGON((150 280, 99 215, 190 210, 150 280))'::geometry AS poly,
'LINESTRING(170 240, 170 240)'::geometry AS line)
SELECT ST_Intersects(poly, line) AS intersects_bad_line,
ST_IsValid(line) AS isvalid_line,
ST_AsText(CASE WHEN NOT ST_IsValid(line) THEN line::box2d::geometry
ELSE line END) as valid_geom,
ST_Intersects(poly, CASE WHEN NOT ST_IsValid(line) THEN line::box2d::geometry
ELSE line END) AS intersects_poly_valid_geom
FROM data;
with results:
NOTICE: Too few points in geometry component at or near point 170 240
NOTICE: Too few points in geometry component at or near point 170 240
NOTICE: Too few points in geometry component at or near point 170 240
-[ RECORD 1 ]--------------+---------------
intersects_bad_line | f
isvalid_line | f
valid_geom | POINT(170 240)
intersects_poly_valid_geom | t
The input data is in EPSG 26918 (UTM zone 18N). So if you want to query against that, you need to use that spatial reference system. If you want to ask in longitude / latitude, you need to transform that into 26918, which is easy to do with ST_Transform.
The conversion looks like:
SELECT ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918)
So if you want to find any area that contains that particular point:
SELECT * FROM es_zones WHERE ST_Contains(geom, ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918))
Best Answer
I would say the geometry you're creating should be in SRID 4326 (the point from Google),
And you should transform the geometry in 2263 to 4326 to match (or really the other way around), so your query might look like this:
That should be close to what you need...