Data: Qualities of NSW Spatial property Layer

Context

This is talking about the uniqueness of the data scraped from here. Generally should should be able to get unique rows with this.

SELECT * FROM (
  SELECT DISTINCT ON (property_id) rid
   FROM nsw_spatial_lppt_raw.property_feature_layer
   WHERE principle_address_type = 1
   ORDER BY property_id, address_string_oid) t
  LEFT JOIN nsw_spatial_lppt_raw.property_feature_layer s USING (rid);

As of 2025-01-09, this returns 2750007 rows, this is oddly more than the number of properties counted by this (2703870):

SELECT COUNT(*)
  FROM (SELECT DISTINCT (property_id) property_id FROM nsw_vg.land_valuation) t; 

rid does not repeat

This should provide a natural and unique id.

When does property_id repeat

• When the super_lot property is Y.
  • I think this is when the valuer general has lumped several properties within the same valuation.
  • The address column seems to be unique as well for each entry.
• When the super_lot property is N.
  • I think this is a single lot, in some cases it may be a large building but it's typically a single building.

What's happening

I think this is when addresses are repeated

select address, count(*) as addr_frequency from (
  select property_id, COUNT(*) as prop_frequency
    FROM nsw_spatial_lppt_raw.property_feature_layer
    where super_lot != 'Y'
    group by property_id
    order by prop_frequency DESC
    limit 1)
  left join nsw_spatial_lppt_raw.property_feature_layer s using (property_id)
  group by address
  order by addr_frequency desc;

The value for prop_frequency is actually 31 here, and the number of rows returned is 31. But this doesn't always seem to be the case?

Shape UUID to property_id

It seem there is only 1 shape_uuid to each property_id.

When property_id Repeats with principle_address_type=1

There seems to be cases where stuff repeats with principle_address_type=1. This seems to be when there are multiple ways of writing the same address, it's possible to get the latest version of an address grouping by property_id

What is principle_address_type

• When this is 1 this seems to be the primary address
  • If I understand correctly there should only be 1 entry amoung several items with the same property_id
  • When you look at the address (from the examples I've seen) there is no unit number.
• When this is 2 this seems to be a secondary property amongst all the residence.