class AddressStreet(db.Model):
'''
Stores information about the relationship between addresses and street segs
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
seg_id = db.Column(db.Integer)
seg_side = db.Column(db.Text)
class ServiceAreaLineSingle(db.Model):
'''
A service area boundary line with a single value for right and left sides.
'''
id = db.Column(db.Integer, primary_key=True)
layer_id = db.Column(db.Text)
source_object_id = db.Column(
db.Integer) # The object ID in the source dataset
seg_id = db.Column(db.Integer)
value = db.Column(db.Text)
class AddressProperty(db.Model):
'''
Stores information about the relationship between addresses and OPA
properties.
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(
db.Text) #, db.ForeignKey('address.street_address'))
opa_account_num = db.Column(
db.Text) #, db.ForeignKey('opa_property.account_num'))
match_type = db.Column(db.Text)
class AddressParcel(db.Model):
'''
Stores information about the relationship between addresses and PWD parcels.
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
parcel_source = db.Column(db.Text)
# Use AIS primary key because parcels don't have unique ID
parcel_row_id = db.Column(db.Integer)
# possible values: base, base_no_suffix, generic_unit,
# parcel_in_address_range, address_in_parcel_range
match_type = db.Column(db.Text)
class AddressZip(db.Model):
'''
Stores information about the relationship between addresses and ZIP ranges.
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
usps_id = db.Column(db.Text)
match_type = db.Column(db.Text)
zip_range = db.relationship(
'ZipRange',
primaryjoin='foreign(ZipRange.usps_id) == AddressZip.usps_id',
lazy='joined',
uselist=False)
class AddressLink(db.Model):
"""
relationship choices:
* in range -- address falls in an official address range
* has base --
* has generic unit --
* matches unit --
74-78 LAUREL ST UNIT 6
"""
id = db.Column(db.Integer, primary_key=True)
address_1 = db.Column(db.Text)
relationship = db.Column(db.Text)
address_2 = db.Column(db.Text)
class AddressTag(db.Model):
"""
Current tags in the database are:
* li_address_key
* info_resident (private)
* info_company (private)
* voter_name (private?)
* pwd_account_num
"""
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
key = db.Column(db.Text)
value = db.Column(db.Text)
class ServiceAreaPolygon(db.Model):
id = db.Column(db.Integer, primary_key=True)
layer_id = db.Column(db.Text)
source_object_id = db.Column(
db.Integer) # The object ID in the source dataset
value = db.Column(db.Text)
geom = db.Column(Geometry(geometry_type='MULTIPOLYGON', srid=ENGINE_SRID))
layer = db.relationship(
'ServiceAreaLayer',
primaryjoin=
'foreign(ServiceAreaLayer.layer_id) == ServiceAreaPolygon.layer_id',
lazy='joined',
uselist=False,
)
class Geocode(db.Model):
"""
Values for `geocode_type` are:
* pwd_parcel
* dor_parcel
* true_range
* centerline
Generally, values should be respected in that order. Centerline
can usually be disregarded.
"""
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
geocode_type = db.Column(db.Text) # parcel, curb, street
geom = db.Column(Geometry(geometry_type='POINT', srid=ENGINE_SRID))
class AddressError(db.Model):
id = db.Column(db.Integer, primary_key=True)
source_name = db.Column(db.Text)
source_address = db.Column(db.Text)
street_address = db.Column(db.Text)
level = db.Column(db.Text)
reason = db.Column(db.Text)
notes = db.Column(db.Text)
class StreetAlias(db.Model):
"""Alternate name for a street segment."""
id = db.Column(db.Integer, primary_key=True)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
street_full = db.Column(db.Text)
seg_id = db.Column(db.Integer)
class ServiceAreaDiff(db.Model):
'''
Layer of all Address Summary points where a difference was observed between
AIS and ULRS. One point per difference.
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
layer_id = db.Column(db.Text)
ais_value = db.Column(db.Text)
ulrs_value = db.Column(db.Text)
distance = db.Column(db.Float)
geom = db.Column(Geometry(geometry_type='POINT', srid=ENGINE_SRID))
class MultipleSegLine(db.Model):
'''
Lines connecting range addresses and segs wherever a range matches to
more than one street seg.
'''
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
parent_address = db.Column(db.Text)
seg_id = db.Column(db.Integer)
parcel_source = db.Column(db.Text)
geom = db.Column(Geometry(geometry_type='LINESTRING', srid=ENGINE_SRID))
class DorParcelErrorPolygon(db.Model):
# Source fields
id = db.Column(db.Integer, primary_key=True)
objectid = db.Column(db.Integer)
mapreg = db.Column(db.Text)
stcod = db.Column(db.Integer)
house = db.Column(db.Integer)
suf = db.Column(db.Text)
stex = db.Column(db.Text)
stdir = db.Column(db.Text)
stnam = db.Column(db.Text)
stdes = db.Column(db.Text)
stdessuf = db.Column(db.Text)
unit = db.Column(db.Text)
shape = db.Column(Geometry(geometry_type='MULTIPOLYGON', srid=ENGINE_SRID))
# Error fields
reasons = db.Column(db.Text)
reason_count = db.Column(db.Integer)
notes = db.Column(db.Text)
class DorParcel(db.Model):
id = db.Column(db.Integer, primary_key=True)
parcel_id = db.Column(db.Text)
street_address = db.Column(db.Text)
address_low = db.Column(db.Integer)
address_low_suffix = db.Column(db.Text)
address_low_frac = db.Column(db.Text)
address_high = db.Column(db.Integer)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
unit_type = db.Column(db.Text)
unit_num = db.Column(db.Text)
street_full = db.Column(db.Text)
source_object_id = db.Column(db.Integer)
source_address = db.Column(db.Text)
geom = db.Column(Geometry(geometry_type='MULTIPOLYGON', srid=ENGINE_SRID))
class DorParcelError(db.Model):
# Source fields
id = db.Column(db.Integer, primary_key=True)
objectid = db.Column(db.Integer)
mapreg = db.Column(db.Text)
stcod = db.Column(db.Integer)
house = db.Column(db.Integer)
suf = db.Column(db.Text)
stex = db.Column(db.Text)
stdir = db.Column(db.Text)
stnam = db.Column(db.Text)
stdes = db.Column(db.Text)
stdessuf = db.Column(db.Text)
unit = db.Column(db.Text)
# Error fields
level = db.Column(db.Text)
reason = db.Column(db.Text)
notes = db.Column(db.Text)
class ZipRange(db.Model):
'''
This is essentially a direct copy of the USPS ZIP+4 table.
'''
id = db.Column(db.Integer, primary_key=True)
usps_id = db.Column(db.Text)
address_low = db.Column(db.Integer)
address_high = db.Column(db.Integer)
address_oeb = db.Column(db.Text)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
unit_type = db.Column(db.Text)
unit_low = db.Column(db.Text)
unit_high = db.Column(db.Text)
unit_oeb = db.Column(db.Text)
zip_code = db.Column(db.Text)
zip_4 = db.Column(db.Text)
class AddressSummary(db.Model):
query_class = AddressSummaryQuery
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
address_low = db.Column(db.Integer)
address_low_suffix = db.Column(db.Text)
address_low_frac = db.Column(db.Text)
address_high = db.Column(db.Integer)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
unit_type = db.Column(db.Text)
unit_num = db.Column(db.Text)
street_full = db.Column(db.Text)
zip_code = db.Column(db.Text)
zip_4 = db.Column(db.Text)
# Foreign keys
seg_id = db.Column(db.Integer)
seg_side = db.Column(db.Text)
pwd_parcel_id = db.Column(db.Text)
dor_parcel_id = db.Column(db.Text)
opa_account_num = db.Column(db.Text)
opa_owners = db.Column(db.Text)
opa_address = db.Column(db.Text)
info_residents = db.Column(db.Text)
info_companies = db.Column(db.Text)
pwd_account_nums = db.Column(db.Text)
li_address_key = db.Column(db.Text)
voters = db.Column(db.Text)
geocode_type = db.Column(db.Text)
geocode_x = db.Column(db.Float)
geocode_y = db.Column(db.Float)
geocodes = db.relationship(
'Geocode',
primaryjoin=
'foreign(Geocode.street_address) == AddressSummary.street_address',
lazy='joined')
tags = db.relationship(
'AddressTag',
primaryjoin=
'foreign(AddressTag.street_address) == AddressSummary.street_address',
lazy='joined')
service_areas = db.relationship(
'ServiceAreaSummary',
primaryjoin=
'foreign(ServiceAreaSummary.street_address) == AddressSummary.street_address',
lazy='joined',
uselist=False)
zip_info = db.relationship(
'AddressZip',
primaryjoin=
'foreign(AddressZip.street_address) == AddressSummary.street_address',
lazy='joined',
uselist=False)
pwd_parcel = db.relationship(
'PwdParcel',
primaryjoin=
'foreign(PwdParcel.street_address) == AddressSummary.street_address',
lazy='joined',
uselist=False)
dor_parcel = db.relationship(
'DorParcel',
primaryjoin=
'foreign(DorParcel.street_address) == AddressSummary.street_address',
lazy='joined',
uselist=False)
opa_property = db.relationship(
'OpaProperty',
primaryjoin=
'foreign(OpaProperty.street_address) == AddressSummary.street_address',
lazy='joined',
uselist=False)
@property
def geocode(self):
"""Returns the "best" geocoded value"""
if not self.geocodes:
return None
priority = {
'pwd_parcel': 4,
'dor_parcel': 3,
'true_range': 2,
'centerline': 1,
}
return max(self.geocodes, key=lambda g: priority[g.geocode_type])
def get_geocode(self, geocode_type):
for g in self.geocodes:
if g.geocode_type == geocode_type:
return g
return None
class StreetSegment(db.Model):
"""A segment of a street centerline."""
id = db.Column(db.Integer, primary_key=True)
seg_id = db.Column(db.Integer)
street_code = db.Column(db.Integer)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
street_full = db.Column(db.Text)
left_from = db.Column(db.Integer)
left_to = db.Column(db.Integer)
right_from = db.Column(db.Integer)
right_to = db.Column(db.Integer)
geom = db.Column(Geometry(geometry_type='LINESTRING', srid=ENGINE_SRID))
# aliases = db.relationship('StreetAlias', back_populates='street_segment')
def __str__(self):
attrs = {
'low': min(self.left_from, self.right_from),
# 'high': max(self.left_to, self.right_to),
'street': self.street_full,
}
return 'StreetSegment: {low} {street}'.format(**attrs)
class SourceAddress(db.Model):
id = db.Column(db.Integer, primary_key=True)
source_name = db.Column(db.Text)
source_address = db.Column(db.Text)
street_address = db.Column(db.Text)
class Address(db.Model):
"""A street address with parsed components."""
query_class = AddressQuery
id = db.Column(db.Integer, primary_key=True)
street_address = db.Column(db.Text)
address_low = db.Column(db.Integer)
address_low_suffix = db.Column(db.Text)
address_low_frac = db.Column(db.Text)
address_high = db.Column(db.Integer)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
unit_type = db.Column(db.Text)
unit_num = db.Column(db.Text)
street_full = db.Column(db.Text)
geocodes = db.relationship(
'Geocode',
primaryjoin='foreign(Geocode.street_address) == Address.street_address',
lazy='joined')
zip_info = db.relationship(
'AddressZip',
primaryjoin=
'foreign(AddressZip.street_address) == Address.street_address',
lazy='joined',
uselist=False)
pwd_parcel = db.relationship(
'PwdParcel',
primaryjoin=
'foreign(PwdParcel.street_address) == Address.street_address',
lazy='joined',
uselist=False)
dor_parcel = db.relationship(
'DorParcel',
primaryjoin=
'foreign(DorParcel.street_address) == Address.street_address',
lazy='joined',
uselist=False)
opa_property = db.relationship(
'OpaProperty',
primaryjoin=
'foreign(OpaProperty.street_address) == Address.street_address',
lazy='joined',
uselist=False)
def __init__(self, *args, **kwargs):
if len(args) == 1:
arg = args[0]
# If a single string arg was passed, parse.
if isinstance(arg, str):
p = parser.parse(arg)
if p['type'] != 'address':
raise ValueError('Not an address')
c = p['components']
# TEMP: Passyunk doesn't raise an error if the street name
# is missing for an address, so check here and do it manually.
if c['street']['name'] is None:
raise ValueError('No street name')
# TEMP: Passyunk doesn't raise an error if the address high is
# lower than the address low.
high_num_full = c['address']['high_num_full']
if high_num_full and high_num_full < c['address']['low_num']:
raise ValueError('Invalid range address')
kwargs = {
'address_low': c['address']['low_num'],
'address_low_suffix':
c['address']['addr_suffix'], # passyunk change
'address_low_frac':
c['address']['fractional'], # passyunk change
'address_high': c['address']['high_num_full'],
'street_predir': c['street']['predir'],
'street_name': c['street']['name'],
'street_suffix': c['street']['suffix'],
'street_postdir': c['street']['postdir'],
'unit_type': c['unit']['unit_type'], # passyunk change
'unit_num': c['unit']['unit_num'], # passyunk change
'street_full': c['street']['full'],
'street_address': c['street_address'],
}
super(Address, self).__init__(**kwargs)
def __str__(self):
return 'Address: {}'.format(self.street_address)
def __repr__(self):
return self.__str__()
def __iter__(self):
for key in ADDRESS_FIELDS:
yield (key, getattr(self, key))
@property
def geocode(self):
"""Returns the "best" geocoded value"""
if not self.geocodes:
return None
priority = {
'pwd_parcel': 4,
'dor_parcel': 3,
'true_range': 2,
'centerline': 1,
}
return max(self.geocodes, key=lambda g: priority[g.geocode_type])
def get_geocode(self, geocode_type):
for g in self.geocodes:
if g.geocode_type == geocode_type:
return g
return None
@property
def zip_code(self):
return self.zip_info.zip_range.zip_code if self.zip_info else None
@property
def zip_4(self):
return self.zip_info.zip_range.zip_4 if self.zip_info else None
@property
def pwd_parcel_id(self):
return self.pwd_parcel.parcel_id if self.pwd_parcel else None
@property
def dor_parcel_id(self):
return self.dor_parcel.parcel_id if self.dor_parcel else None
@property
def opa_account_num(self):
return self.opa_property.account_num if self.opa_property else None
@property
def opa_owners(self):
return self.opa_property.owners if self.opa_property else None
@property
def opa_address(self):
return self.opa_property.source_address if self.opa_property else None
@property
def parity(self):
low = self.address_low
high = self.address_high
if high:
return parity_for_range(low, high)
else:
return parity_for_num(low)
@property
def address_full(self):
"""Returns full primary address (e.g. 1003R-07 1/2)"""
address_full = str(self.address_low)
if self.address_low_suffix:
address_full += self.address_low_suffix
if self.address_high:
address_full += '-' + str(self.address_high)[-2:]
if self.address_low_frac:
address_full += ' ' + self.address_low_frac
return address_full
@property
def address_full_num(self):
'''
Returns all numeric components of address. Example:
1234A-36 1/2 => 1234-36
'''
num = str(self.address_low)
if self.address_high:
address_high = str(self.address_high)
if len(address_high) < 2:
# address_high = '0' + address_high
num += '-' + str(address_high)
else:
num += '-' + str(address_high)[-2:]
return num
@property
def base_address(self):
return ' '.join([self.address_full, self.street_full])
@property
def base_address_no_suffix(self):
return '{} {}'.format(self.address_full_num, self.street_full)
@property
def is_base(self):
if self.unit_type or self.address_low_suffix:
return False
return True
@property
def is_basic_range(self):
"""
Determines if an address is a basic range, i.e. it has an address
high but no other secondary components like suffix, fractional, or unit.
"""
is_range = self.address_high is not None
non_basic_comps = [
self.address_low_suffix,
self.address_low_frac,
self.unit_type,
]
is_basic = all(comp is None for comp in non_basic_comps)
return is_range and is_basic
@property
def child_addresses(self):
"""Returns a list of individual street addresses for a range"""
address_low_re = re.compile('^{}'.format(self.address_low))
address_high_re = re.compile('-\d+')
child_addresses = []
for child_num in self.child_nums:
child_num = str(child_num)
child_street_address = address_low_re.sub(child_num, \
self.street_address)
child_street_address = address_high_re.sub('',
child_street_address)
child_address = Address(child_street_address)
child_addresses.append(child_address)
return child_addresses
@property
def child_nums(self):
"""Returns a list of individual address nums for a range"""
if not self.is_basic_range:
return []
child_num_list = []
for x in range(self.address_low, self.address_high + 1, 2):
child_num_list.append(x)
return child_num_list
@property
def generic_unit(self):
if self.unit_type is None:
return self.street_address
address_full = self.address_full
street_full = self.street_full
unit_type = '#'
unit_num = self.unit_num
return ' '.join([x for x in [address_full, street_full, unit_type, \
unit_num] if x])
@property
def hundred_block(self):
address_low = self.address_low
return address_low - (address_low % 100)
@property
def unit_full(self):
if self.unit_type:
unit_full = self.unit_type
if self.unit_num:
unit_full = ' '.join([unit_full, self.unit_num])
return unit_full
return None
class ServiceAreaLayer(db.Model):
id = db.Column(db.Integer, primary_key=True)
layer_id = db.Column(db.Text)
name = db.Column(db.Text)
description = db.Column(db.Text)
class ParcelCurb(db.Model):
id = db.Column(db.Integer, primary_key=True)
parcel_source = db.Column(db.Text)
# Use AIS primary key because parcels don't have unique ID
parcel_row_id = db.Column(db.Text)
curb_id = db.Column(db.Integer)
class Curb(db.Model):
id = db.Column(db.Integer, primary_key=True)
curb_id = db.Column(db.Integer)
geom = db.Column(Geometry(geometry_type='MULTIPOLYGON', srid=ENGINE_SRID))
class OpaProperty(db.Model):
id = db.Column(db.Integer, primary_key=True)
account_num = db.Column(db.Text)
street_address = db.Column(db.Text)
address_low = db.Column(db.Integer)
address_low_suffix = db.Column(db.Text)
address_low_frac = db.Column(db.Text)
address_high = db.Column(db.Integer)
street_predir = db.Column(db.Text)
street_name = db.Column(db.Text)
street_suffix = db.Column(db.Text)
street_postdir = db.Column(db.Text)
unit_type = db.Column(db.Text)
unit_num = db.Column(db.Text)
street_full = db.Column(db.Text)
source_address = db.Column(db.Text)
tencode = db.Column(db.Text)
owners = db.Column(db.Text)
