[postgis-users] Comparing two Building Footprint Tables

[Sandro Santilli]

Consider also ST_HausdorffDistance:
http://postgis.net/docs/ST_HausdorffDistance.html

--strk;

On Tue, Sep 02, 2014 at 10:52:37AM +0200, Rémi Cura wrote:
> Sorry for double post.
> I meant it is certainly a better distance measure.
> You could simply replace"computing_score"
> with :
> follwoing, and adapt threshold.
> computing_score AS ( --for each potential matching, compute the score of
> this pair
>     SELECT id_a , id_b
>         , ST_Area(ST_Intersection(geom_
> a,geom_b))/ST_Area(ST_Union(geom_a,geom_b)) as score
>              --note : can be replaced by something more subtle if you have
> more attributes / measure
>              --note : two identical buildings would have a big score, two
> totally different vuilding would have a score of 0
>         , LOWEST(ST_Area(geom_a),ST_Area(geom_b)) AS lowest_area --we keep
> it for latter
>     FROM geometry_filtering
> )
> 
> 
> 2014-09-02 10:50 GMT+02:00 Rémi Cura <remi.cura at gmail.com>:
> 
> > My colleague who is finishing a phd in precisely this database maping
> > (historical comparison of street network) also recommands using
> > Area(Intersection(A,B)) / area(Union(A,B)).
> >
> > It is certainly a best solution.
> > Cheers,
> > Rémi-C
> >
> >
> >
> > 2014-09-01 23:56 GMT+02:00 Paul Ramsey <pramsey at cleverelephant.ca>:
> >
> > My favourite "are polygons the same" test is the ratio of the
> >> Intersection(A,B) over Union(A,B). If they are identical, the ratio
> >> will be 1. If they are disjoint, the ratio will be 0. If you choose a
> >> fairly high ratio, you should get polygons that are very much the
> >> same, thus allowing for minor errata and slop.
> >>
> >> SELECT a.id AS old_id, b.id AS new_id
> >> FROM old_shapes a
> >> JOIN new_shapes b
> >> ON ST_Intersects(a.geom, b.geom)
> >> WHERE ST_Area(ST_Intersection(a.geom, b.geom)) /
> >> ST_Area(ST_Union(a.geom, b.geom)) > 0.95;
> >>
> >> P
> >>
> >>
> >> On Sat, Aug 23, 2014 at 10:54 PM, Clifford Snow <clifford at snowandsnow.us>
> >> wrote:
> >> > I had considered using addressing data to give each footprint a unique
> >> id,
> >> > but yours does the same with one less step.  i'm going to try Rémi-C
> >> > solution as well.
> >> >
> >> > Thanks
> >> >
> >> >
> >> > On Sat, Aug 23, 2014 at 3:51 AM, David Siegel <david.siegel at artcom.de>
> >> > wrote:
> >> >>
> >> >> Hi Clifford
> >> >>
> >> >> just a quick saturday morning thought: One approach is to use a hash
> >> >> function like SHA256. To get started I’d try something like this:
> >> >>
> >> >> 1. For each footprint get a text representation of the geometry
> >> >> 2. Compute and store the SHA256[1] hash of the text representation
> >> >> 3. Find those hash values that are unique to the new footprint table
> >> >>
> >> >> Note that you can influence the result by changing the text
> >> >> representation. Using a sorted list of just the coordinates for example
> >> >> ignores any changes in topology, &c. To speed things up just add an
> >> index on
> >> >> the hash column.
> >> >>
> >> >> Cheers,
> >> >>
> >> >> d
> >> >>
> >> >> [1] http://www.postgresql.org/docs/8.3/static/pgcrypto.html
> >> >>
> >> >> On 23.08.2014, at 00:49, Clifford Snow <clifford at snowandsnow.us>
> >> wrote:
> >> >>
> >> >> > The city of Seattle published a new shapefile with the latest
> >> building
> >> >> > footprints. The previous file was from 2009. I'd like to find which
> >> outlines
> >> >> > have changed. While I can see the differences using QGIS, what I
> >> need is a
> >> >> > list of changed or new building footprints. I'm struggling where to
> >> start.
> >> >> > There are about 300K footprints. The city did not retain any unique
> >> ids from
> >> >> > the previous file to the new.
> >> >> >
> >> >> > Any suggestions would be appreciated.
> >> >> >
> >> >> > Thanks,
> >> >> > Clifford
> >> >> >
> >> >> > --
> >> >> > @osm_seattle
> >> >> > osm_seattle.snowandsnow.us
> >> >> > OpenStreetMap: Maps with a human touch