joining RTT and OPA

[Katharine Nelson]

Hello!

I'm currently trying to match the RTT data to the OPA data so that I can figure out whether deeds are for residential or other types of properties.

I de-duped the RTT grabbing only the most recent deed associated with any OPA account number. Then I tried to join using the opa_account_num field from RTT and the parcel_number from OPA. I was disappointed to only get a 60% join rate. 

Does anyone have any suggestions or best practices for joining these two datasets?

Thank you for any help you can offer!

Katie

[Katharine Nelson]

Hi again,

I figured out the pin rather than parcel_number field from OPA fixed the issue. Hopefully these posts can help someone else problem solve along the way!

Thanks,

Katie

--
You received this message because you are subscribed to a topic in the Google Groups "Open Data Philly" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/opendataphilly/ncs9yXrb5AQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to opendataphill...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/4d60b3ac-ae2b-4c5f-83b6-d959d5faade9n%40googlegroups.com.

[Katharine Nelson]

Hello again,

Sorry to keep reaching out but there's something funky going on with the OPA data on Open Data Philly. The CSV files over the last couple of weeks only have roughly 200,000 records in them, which is less than half as many records as there are parcels in the City.

Does anyone know which of the data formats available is most likely to contain a complete universe of parcels? As of 12/15/2022, the geodatabase has 580,000 records in it, and the GeoJSON file has 550,000 records in it. I think there should be close to 600,000 parcels in the City, correct?

Also, if anyone has tips on who at OPA to contact about the problem with the CSV files in particular that would be much appreciated.

Thanks,

Katie Nelson

[Robert Cheetham]

Katie,

I think your estimate of 580K - 600K sounds about right. I'm not sure what the issue was but on downloading it tonight, there are ~581K available in the CSV. 

For questions about OPA data, the OpenDataPhilly catalog indicates you can email opa...@phila.gov.

Best,

Robert

------------------

Robert Cheetham

Azavea

You received this message because you are subscribed to the Google Groups "Open Data Philly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opendataphill...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/5d974406-2c5a-43d2-9f4a-386126a70d2fn%40googlegroups.com.

[Reed Benet]

Hello. To determine military vet and adult non-military vet population in the Philly City Council Districts, I'm looking to match the overlaps of City Council Districts and Census Tracts. 

I can get the vet and non-military vet populations in Census Tracts via a S2101 Veterans Status in Advanced Search.

As per https://seventy.org/political-maps-of-philadelphia/city-council-districts, I found the overlaps of City Council Districts and Wards and Divisions.

And as per https://www.opendataphilly.org/dataset/property-parcels/resource/7037fb9b-af0f-4b43-9a38-208aa10e41fc, I can find the overlaps of Wards and Census Tracts.

And as per the attached, I've combined the two data sources above into one excel spreadsheet, but perhaps there's a cleaner and simpler and available data source for what I'm looking for other than just eyeballing a map of Census Tracts available via https://www2.census.gov/geo/maps/DC2020/PL20/st42_pa/censustract_maps/c42101_philadelphia/DC20CT_C42101.pdf with the map available at the first link above?

Related, is there a listing of parcels that shows the City Council Districts they are in?

Thanks!

--

Reed M. Benet

Founder/CEO

zeroto6t, inc. DBA herohomes.com

re...@herohomes.com

(415) 342-3634

"There is no frigate like a book to take us lands away..." -- Emily Dickinson

[ni...@property-square.com]

These are the sort of questions that Geographic Info Systems (GIS) applications are built to answer. They will have functions to identify if a place is within another or if parcels share a border.

There are 2 free ones I know of: QGIS.org and PostGIS.net.
Then you use the shapefile of gdb file downloads from open data sources as input.

Hope this helps.

-Nick

Reed M. Benet

--
You received this message because you are subscribed to the Google Groups "Open Data Philly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opendataphill...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/CADs_-66rr0UbNX1D8FVDapwJr_DYiGAj50TRoSxVQZiP_V5SUw%40mail.gmail.com.

[Reed Benet]

Thanks, Nick. I downloaded QGIS, and it looks great. But a bit much to learn how to use to answer a relatively simple question if there was a resource that showed the census tracts in the city council districts. I could have sworn that the parcel data used to have a column showing city council district, but maybe I was just dreaming...  ;-)

To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/009301d91a03%24d56720f0%24803562d0%24%40property-square.com.

--

Reed M. Benet

Founder/CEO

zeroto6t, inc. DBA herohomes.com

re...@herohomes.com

(415) 342-3634

"There is no frigate like a book to take us lands away..." -- Emily Dickinson

[ni...@property-square.com]

Reed,

 

QGIS is pretty daunting. I generally use a PostGIS and postgresql combo to pull the files from Open Philly (using the SRID of 2272 to synch the location data) into tables. PostGIS generates a geom (meaning a geolocation-code) column in the table that the program uses to identify locations of records in relation to other records, such whether a parcel is within a ward, zipcode, or perhaps council district . That combo has fewer buttons and menus, but still has a learning curve that might be higher than necessary to get your answer if you can find another source.

 

The OPA table has ward data associated with parcels. It looks as if the Committee of 70 link might provide the listing you need to go from the OPA wards to the Districts.

 

Good luck!

To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/CADs_-66G6wt%2BKf994w3g3J3Wc%2BEJguiptD9mAh-2aAdXnE4Xrg%40mail.gmail.com.

[Katharine Nelson]

Dear Robert,

Thank you for your response. 

I just downloaded the csv from opendataphilly and there are only 191506 records in it. I'm planning to use the geodatabase which has 581,000 or so records, but when I join it to the RTT data there are still ten thousand or so unique records in the RTT that do not join, even though the parcel numbers seem to exist. So, I do think there is something going on here; maybe a server timeout or something? I'll reach out to OPA and maybe do a spatial analysis with the gdb to see if I can figure out what's missing when I get some time.

Best,

Katie

[odph...@nym.hush.com]

Hi Katie and all,

I'm just dipping my toes into opendataphilly, and started by trying to understand what you were doing and to reproduce your numbers. It's very possible that I'm out of my depth here. With that said ...

For quick and dirty numbers from files downloaded 3 Jan 2022, I found 581904 records for the CSV and 582027 for the geojson. Here's my approach:

# Might not be unique
$ wc opa_properties_public.csv
581904 16583230 283090279 opa_properties_public.csv

# These are unique
$ cat opa_properties_public.geojson|jq -r --stream 'select((.[0]|length)==4 and (.[0][3]=="parcel_number"))|.[1]'|sort|uniq|wc
582027 582027 5820270

I'm interested if you might share your (likely more advanced) tools and approach.

En paz,
Steve Thompson

[Phil Cochetti]

Hey Katie, 

I ran into an issue with loading the CSV due to a few minor data inconsistency issues, I suspect you may have been running into the same. 

The property data has inconsistent double quotation marks around text when that text includes unescaped text characters. It does not employ them when an offending character is not present. This inconsistency was a problem for many import wizards and import scripts. Alternatively, others were okay with the inconsistent quotes however they then choked on an unmatched single quotation mark. I was getting errors that there were too few or too many data columns for the table and it would stop loading the data at an offending row. 

In MySQL, I had to add the two highlighted lines to my SQL script to handle the inconsistency. I also needed to process the datetime stamps so they were interpretable as datetime and not text during the INSERT (in blue). 

Here's some MySQL syntax that I used to successfully load the full dataset from CSV in October. 

Phil

LOAD DATA INFILE 'opa_properties_public.csv'
INTO TABLE `phillylicenses3`.`property`
CHARACTER SET utf8mb4
fields terminated by ","
OPTIONALLY ENCLOSED BY '"'
lines terminated by '\n'
IGNORE 1 LINES

(`objectid`,
@dateassess,
`basements`,
`beginning_point`,
`book_and_page`,
`building_code`,
`building_code_description`,
`category_code`,
`category_code_description`,
`census_tract`,
`central_air`,
`cross_reference`,
@dateexterior,
`depth`,

.

.

.

}

 SET assessment_date = str_to_date( @dateassess, '%Y-%m-%d %H:%i:%s' ),
   market_value_date = str_to_date( @datemarket, '%Y-%m-%d %H:%i:%s' ),
   sale_date = str_to_date( @datesale, '%Y-%m-%d %H:%i:%s' ),
   date_exterior_condition = str_to_date( @dateexterior, '%Y-%m-%d %H:%i:%s' ),
   recording_date = str_to_date( @daterecording, '%Y-%m-%d %H:%i:%s' );
   
   

[Donkiss Zidane]

Buy Albino Penis Envy Mushrooms, or “APE,” is a genetically isolated, non-pigmented strain of the famous Polkadot chocolate. But the lack of pigment gives the albino penile envy mushroom a bright white appearance.
https://t.me/EgUNSNP43FY3MDJk
https://t.me/EgUNSNP43FY3MDJk
Your best online shop to get platinum quality microdosing psychedelics products online, pain,anxiety pills, and research chemicals.
Be 100% assured about the quality and genuineness of the product, and you will also be able to buy quality psychedelics products at a fair price.
https://t.me/EgUNSNP43FY3MDJk/7443?single
https://t.me/EgUNSNP43FY3MDJk/6684?single
Dmt For Sale

Xannax For Sale

Disposables For Sale

Shatter For Sale

Wax For Sale

Mushroom For Sale

Chocolate bars For Sale

Edibles For Sale

Vape pens For Sale

Adderall For Sale

M30 For Sale

Coke For Sale

Gummies For Sale

Hash For Sale

Pre-Rolls For Sale

Exotic Buds For Sale

Clone Cards For Sale

Benzos For Sale


Place your orders now. Shop Below
https://t.me/EgUNSNP43FY3MDJk


And More Related Products Also Available On Deck. Shop👇
https://t.me/EgUNSNP43FY3MDJk
Contact my telegram Usernames @Caliibudss
https://t.me/EgUNSNP43FY3MDJk
You can contact me through my telegram Usernames: @Caliibudss
https://t.me/EgUNSNP43FY3MDJk
Telegram Channel Link In Bio . Let's Keep winning 💯 💪