[postgis-users] Comparing two Building Footprint Tables

[Rémi Cura]

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
> >> > _______________________________________________
> >> > postgis-users mailing list
> >> > postgis-users at lists.osgeo.org
> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >
> >
> >
> >
> > --
> > @osm_seattle
> > osm_seattle.snowandsnow.us
> > OpenStreetMap: Maps with a human touch
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140902/d6ab24fd/attachment.html>