<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 11.00.10570.1001"></HEAD>
<BODY>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>Hi
All,</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=400023022-26032023></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>I have
two tables, a parcel (polygon) table and a building
(polygon) table in a PostGIS db. <BR>There are about 1 million parcels
and over a million buildings in these tables ...</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>I have
set gist indexes on both and clustered each on a geohash, pkey is set on
objectid.<BR></DIV></SPAN></FONT>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>I
would like to fill 2 additional columns for the parcels layer to have the number
of building on each parcel (building_count) and the summed up area of
all those building footprints (building_fp).</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=400023022-26032023>Below is an update query but it is
extremely lengthy and inefficient (on a beefy 16 core Ubuntu server with 128 GB
tons of RAM) : I had to stop an after running 3 days when it was not
finished ...</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>I am
wondering if anyone has an idea how to make it more efficient
?</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=400023022-26032023></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=400023022-26032023>This
is the query:</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=400023022-26032023></SPAN></FONT> </DIV>
<DIV><FONT color=#800000 size=2 face=Arial><SPAN class=400023022-26032023>UPDATE
parcels p SET<BR>building_count = subquery.num,<BR>building_fp =
subquery.sumarea<BR>from<BR>(select objectid, count(*) as num,
sum(st_area(b.geom)) as sumarea FROM parcels p<BR>left join buildings b on
st_intersects(p.geom,st_centroid(b.geom)) group by objectid ) subquery<BR>where
p.objectid = subquery.objectid ;</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial></FONT> </DIV>
<DIV><SPAN class=400023022-26032023><FONT color=#000080 size=2 face=Arial>Any
suggestions ?</FONT></SPAN></DIV>
<DIV><SPAN class=400023022-26032023><FONT color=#000080 size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=400023022-26032023><FONT color=#000080 size=2
face=Arial>Cheers</FONT></SPAN></DIV>
<DIV align=left>
<DIV><SPAN style="FONT-SIZE: 11pt; FONT-FAMILY: 'Verdana','sans-serif'">Karsten
Vennemann<BR></SPAN></DIV></DIV></BODY></HTML>