Acella: non coincident parcel address problem

This page is to describe and discuss the non coincident parcel address problem. 

Definition

This happens when an address is linked to a parcel that is not spatially coincident.
A picture is worth a thousand words.

This is "1011 Silliman St" which is linked to 5935-027 and 5935-028.
To see this live go to http://eas.sfgov.org.

EAS originally did not support this but we changed EAS when we found these data in AVS. 
Acella does not support this sort of address.
In Acella, parcels for an address must be spatially coincident with the address point.

Variation

There is quite a bit of variation to these data.

This one has 3 contiguous parcels.

This one is complicated and involves retired parcels.

 
This one is non-contiguious and seems likely to be an error of some sort. 

The Universe

Here is a spreadsheet that contains all of these addresses.
You can see these in detail at http://eas.sfgov.org.
There are 3253 unretired parcel level addresses currently being used at DBI. 
This is out of some 300,000 plus addresses. 

Query

We can find these easily with a query like this.

SELECT
	ab.base_address_num,
	ab.base_address_suffix,
	a.unit_num,
	sn.base_street_name,
	sn.street_type,
	p.blk_lot
FROM address_base ab
inner join addresses a on (ab.address_base_id = a.address_base_id)
inner join streetnames sn on (ab.street_segment_id = sn.street_segment_id)
inner join street_segments ss on (ab.street_segment_id = ss.street_segment_id)
left outer join address_x_parcels axp on (axp.address_id = a.address_id)
left outer join parcels p on (axp.parcel_id = p.parcel_id)
WHERE 1 = 1
and sn.category = 'MAP'
and ab.retire_tms is null
and a.retire_tms is null
and axp.retire_tms is null
and not st_intersects(ab.geometry, p.geometry)
order by
	ss.st_name,
	ss.st_type,
	ab.base_address_num,
	ab.base_address_suffix,
	a.unit_num;