CityOfPhiladelphia / ais

Address Information System
19 stars 8 forks source link

Create true range view in DB bootstrap script #27

Open bertday opened 8 years ago

bertday commented 8 years ago

Add a create statement for this view:

SELECT COALESCE(r.seg_id, l.seg_id) AS seg_id,
   r.low AS true_right_from,
   r.high AS true_right_to,
   l.low AS true_left_from,
   l.high AS true_left_to
  FROM ( SELECT asr.seg_id,
           min(a.address_low) AS low,
           GREATEST(max(a.address_low), max(a.address_high)) AS high
          FROM address a
            JOIN address_street asr ON a.street_address = asr.street_address
         GROUP BY asr.seg_id, asr.seg_side
        HAVING asr.seg_id IS NOT NULL AND asr.seg_side = 'R'::text) r
    FULL JOIN ( SELECT asl.seg_id,
           min(a.address_low) AS low,
           GREATEST(max(a.address_low), max(a.address_high)) AS high
          FROM address a
            JOIN address_street asl ON a.street_address = asl.street_address
         GROUP BY asl.seg_id, asl.seg_side
        HAVING asl.seg_id IS NOT NULL AND asl.seg_side = 'L'::text) l ON r.seg_id = l.seg_id
 ORDER BY r.seg_id
bertday commented 7 years ago

Currently being created in load_addresses on this line.

This should go into /ais/engine/bin/create_db.bat so it's only created once when the engine environment is set up.