Hello All,

I'm trying to understand how to use ST_Transform() on a query. When I have everything in the same projection (in this case Albers Equal Area Conic) my query returns the correct result:

SELECT
    SUM((stats).sum)
FROM (
    select       
        ST_SummaryStats(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) as stats
    FROM        
        ny_albers as r,
        cities_albers as p
    WHERE
        p.name = 'new_york'
    AND
        ST_Intersects(r.rast, p.geom)
    )  as foo;

If I use a table with polygons in a different projection (in this case Web Mercator), I do not get any values returned:

SELECT
    SUM((stats).sum)
FROM (
    select       
        ST_SummaryStats(ST_Clip(r.rast, 1, ST_Transform(p.geom,102003), NULL, TRUE)) as stats
    FROM        
        ny_albers as r,
        cities_webMercator as p
    WHERE
        p.name = 'new_york'
    AND
        ST_Intersects(r.rast, ST_Transform(p.geom,102003))
    )  as foo;

I added the projection SRID:102003 using the following lines:

INSERT into
    spatial_ref_sys (
    srid,
    auth_name,
    auth_srid,
    srtext,
    proj4text
    )
values (
     102003, -- I changed this line as it was originally 9102003 which did not satisfy the constraints
     'esri',
     102003,      'PROJCS["USA_Contiguous_Albers_Equal_Area_Conic",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Albers_Conic_Equal_Area"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["longitude_of_center",-96],PARAMETER["Standard_Parallel_1",29.5],PARAMETER["Standard_Parallel_2",45.5],PARAMETER["latitude_of_center",37.5],UNIT["Meter",1],AUTHORITY["EPSG","102003"]]',
     '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=37.5 +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs ');

I had some problems when I tried to add this projection from http://spatialreference.org/ref/esri/102003/postgis/
The names and values of srtext, and proj4text were reversed, and the SRID seemed incorrect so I changed it. I don't know if this is related to my question above, but I thought it worth mentioning. I did import the raster and polygon using this SRID on the query that worked.

Thank you,
David

Dear all,