[postgis-users] Comparing two Building Footprint Tables

[Rémi Cura]

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
>> >> > _______________________________________________
>> >> > 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/3ae0ebfc/attachment.html>