[postgis-users] Comparing two Building Footprint Tables

[Paul Ramsey]

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