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 <pram...@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 <cliff...@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.sie...@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 <cliff...@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@lists.osgeo.org > >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > >> > >> _______________________________________________ > >> postgis-users mailing list > >> postgis-users@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@lists.osgeo.org > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users