Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 11 Next »

This set of remaining ETL issues has ramifications that go well beyond the ETL itself.
The Right of Way Address issue a a good example (details below).
With that in mind I think it is vitally important that we focus on this question...
what is the bare minimum set of features and data that we need to make a viable 1.1 release?

"right of way" addresses

Description
There are a number of AVS addresses that use a non-existent block lot (e.g. 0000-000?)
These address do have a valid street and street number.
These addresses are called "right of way addresses".
The ETL is currently rejecting these addresses (no matching block - lot).
The EAS application is designed to prevent addresses from being created outside a parcel boundary.
We were not planning on supporting this type of address until a later release.
DPW wants this feature as well.

Option 1 (increase scope)
Use a "right of way" polygon map layer to support this feature
There are a couple of challenges

  • the "right of way" polygon layer is not maintained as is the parcel layer (can we automate?)
  • we would have to write some additional ETL code - possibly non-trivial

Option 2 (maintain scope)
Defer loading these addresses from AVS until the next release.
I assume we would have to focus on this issue in the very next release.

EAS missing some streets

When anyone finds that streets are missing from EAS or are incorrect,
EAS users shall notify Marivic at DPW. Maravic will make the corrections
which should appear in EAS the following day.

no such street name - number is non-zero (SITUS TO BE ASSIGNED, UNKNOWN)

We can supply the correct street and street type.
Val, I assume that we should simply build the SQL for this...something like:

  • update addresses set street_name = x, street_type = y, where id = z;

no such street name - number is zero (SITUS TO BE ASSIGNED, UNKNOWN)

Option 1

  • create a dummy street record for the UNKNOWN street
  • duplicates are not allowed, so we would have to create 0..N UNKNOWN
  • we would provide update statements such as
    • update addresses set number where id = x;
      Option 2
      We can programatically select a good approximation.
      Details
  • query to get the street nearest the parcel centroid
  • query to find the point on the street that is nearest the parcel centroid
  • use that point and interpolation to choose the best address number
  • no duplicates!
  • spatial query to determine right/left side (then even/odd number)
  • we would provide update statements such as
    • update addresses set number, street, street_type where id = x;

Bear in mind here that this solution means at least 2 iterations of of the ETL process
First time through these addresses get excepted - and we generate the SQL.
The SQL is then applied to AVS.
On iteration 2 the rows are imported.

I like option 2 - seems likes a cleaner solution.
Amount of work for either option seems like about 2-3 days.

invalid street suffix

We agreed that in specific limited circumstances we would update street suffixes in AVS.

  • e.g. Stanyan Blvd vs Stanyan St (nearest one is Blvd but AVS says St - we should choose BLVD)
  • e.g. Broadway vs Broadway St (there is no Broadway St - only alternative is Broadway)

Link to Recorded Maps From APN

Say that we are using the EAS user interface to examine the address at APN 1234001.
This APN resolves to 1301 Page St.
Clicking on "Details" will resault in the address details panel showing on map.
From that panel you can click on an APN and see this page: http://gispub02.sfgov.org/website/sfparcel/showlotinfo.asp?LotIDValue=1234001
According to Vivian, this is not exactly what we want to show at this point.
Instead we want to show the recorded map(s).
We think we know what to show and we would like Vivian to confirm.
So generally we think she wants to use this site:
http://bsm.sfdpw.org/subdivision/recorded/
We can hack the URL a bit an provide this:
http://bsm.sfdpw.org/subdivision/recorded/recordedBottom.asp?block=1234&lot=001&streetname=&map=&book=&page=&box=&project=&projectid=
which returns nothing. We can also just enter the block and show that
http://bsm.sfdpw.org/subdivision/recorded/recordedBottom.asp?block=1234&lot=&streetname=&map=&book=&page=&box=&project=&projectid=
From there if you follow the links I think you can (eventually) see the recorded map(s) - for example
http://bsm.sfdpw.org/subdmap/subd/T44/T44032a.TIF
On that last link you may need to tell your browser how to handle a tiff image file.
This is not terribly elegant but it might suffice.

provide additional info for certain exceptions

Ideally we would like to fix any bad data we come across.
The exceptions that say:
"insert rejected - address_x_parcels - address geometry is not contained by the specified parcel"
are a bit tricky - but can we do something with these?

Case 1: 0 Alvord (7 exceptions)
What should happen here is that we create 7 base addresses (0 alvord, 2 alvord...12 alvord).
We would have to do slightly fancy spatial processing but it is possible.
SQL would be generated by the ETL have to be applied in AVS and the ETL process run a second time to pick up the cleaned up data.

Case 2:
Need more info here

address_kind

todo

address_type

todo

  • No labels