[postgis-users] raster intersecting geometry

[Regina Obe]

You don't need to use ST_GeomFromText if it's already a geometry, if you
need to set the SRID to 4326 then use ST_SetSRID(geom, 4326), but I don't
think you need that either.

Couple of other things I see as issues with this query

 

1)      ST_Buffer is missing an amount to buffer by, if your coordinates are
in 4326 then your units would be in degrees so should be changed to
something like 

ST_Buffer(geom, 0.00001) 

2)      ST_Buffer(. FROM ch04.newpoint)  should not be like that.  I'm
assuming your ch04.newpoint is the table of the points you want to intersect
with

So should be more like:

  FROM ch04.dumprastval AS r INNER JOIN ch04.newpoint AS p ON
ST_Intersects(r.rast, p.geom)

3)      Gathering from the name ch04.dumprastval, it sounds like you may
have already run ST_DumpAsPolygons to convert your raster to a geometry?  In
which case you shouldn't have a rast column at all.

4)      While not required, it helps to understand what is going on if you
prefix the columns with the table each is coming from.  For example in #2 I
was guessing. If you tables are kinda long, it helps to alias them such as

ch04.newpoint AS p

 

 

Once you have a revised query it helps to post the error you are having
since it's hard to test your query without data.

 

Hope that helps,

Regina 

 

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Amy O'Keefe
Sent: Friday, November 6, 2020 7:50 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: [postgis-users] raster intersecting geometry

 

Hi all,

could anyone help me with the below query?

I'm trying to find the geometries from a table (ch04.newpoint) which
intersect the raster (ch04.dumprastval).

 

However I can't seem to get it to work!

 

Thanks.

 

 

 

SELECT 

CAST((gval).val As integer) AS val,

ST_Union((gval).geom) As geom

FROM (

SELECT ST_Intersection(

ST_Clip(rast,ST_Envelope(buf.geom)),

1,

buf.geom

) As gval

FROM ch04.dumprastval

INNER JOIN (

SELECT ST_Buffer(

ST_GeomFromText(geom,4326) FROM ch04.newpoint

ON ST_Intersects(rast,geom)

) As foo

GROUP BY (gval).val

ORDER BY (gval).val;



This email and any attachments are confidential and intended solely for the
use of the addressee and may contain information which is covered by legal,
professional or other privilege. If you have received this email in error
please notify the system manager at postmaster at ulster.ac.uk
<mailto:postmaster at ulster.ac.uk>  and delete this email immediately. Any
views or opinions expressed are solely those of the author and do not
necessarily represent those of Ulster University. 
The University's computer systems may be monitored and communications
carried out on them may be recorded to secure the effective operation of the
system and for other lawful purposes. Ulster University does not guarantee
that this email or any attachments are free from viruses or 100% secure.
Unless expressly stated in the body of a separate attachment, the text of
email is not intended to form a binding contract. Correspondence to and from
the University may be subject to requests for disclosure by 3rd parties
under relevant legislation. 
The Ulster University was founded by Royal Charter in 1984 and is registered
with company number RC000726 and VAT registered number GB672390524.The
primary contact address for Ulster University in Northern Ireland is Cromore
Road, Coleraine, Co. Londonderry BT52 1SA 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20201106/b155d64b/attachment.html>