search

codeforkansascity / address-api

A address database accessible through an API that will return attributes about the address, neighborhood, council district, zoning...
http://address-api.codeforkc.org
MIT License
5 stars 9 forks source link

Load Jackson County Building Property Data Set - 2 #40

Open zmon opened 9 years ago

zmon commented 9 years ago 
SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
 FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom)::json As geometry
    , row_to_json(lp) As properties
   FROM address_spatial.census_metro_areas As lg
         INNER JOIN (SELECT gid, name FROM address_spatial.census_metro_areas) As lp      
       ON lg.gid = lp.gid  ORDER BY lg.name) As f )  As fc;
rlh-aagis commented 9 years ago

This is good news Paul! On Sep 24, 2015 10:38 PM, "Paul D Barham" notifications@github.com wrote:

-

Get shape file cb_2014_us_cbsa_500k.zip - Had error

Load shape file into temporary table address_spatial.census_metro_area_tmp

shp2pgsql -g geom \ -I /var/wwwsites/dev-api.codeforkc.org/data/jackson_county_mo/set-2/Parcels_andAddresses::_Jackson_County_MO.shp \ address_spatial.jackson_cnt_mo_2_tmp | psql -d code4kc

this results in

                                   Table "address_spatial.jackson_cnt_mo_2_tmp"

Column | Type | Modifiers ------------+------------------------+------------------------------------------------------------------------------------ gid | integer | not null default nextval('address_spatial.jackson_cnt_mo_2_tmp_gid_seq'::regclass) objectid | numeric(10,0) | owner | character varying(80) | shapestare | numeric | shapestlen | numeric | shape_star | numeric | shape_st_1 | numeric | shape_stle | numeric | shape_st_2 | numeric | geom | geometry(MultiPolygon) | Indexes: "jackson_cnt_mo_2_tmp_pkey" PRIMARY KEY, btree (gid) "jackson_cnt_mo_2_tmp_geom_gist" gist (geom) ````

select gid, objectid, owner, shapestare, shapestlen from address_spatial.jackson_cnt_mo_2_tmp limit 10; -[ RECORD 1 ]--------------------------------------- gid | 1 objectid | 1001 owner | RICE PAUL & JENNIFER shapestare | 9368.024019000000408 shapestlen | 415.248697999999990 -[ RECORD 2 ]--------------------------------------- gid | 2 objectid | 1002 owner | ALBERT JERRY DEAN & MARGARET R-TRUSTEE& shapestare | 10449.461418999999296 shapestlen | 424.035738999999978 -[ RECORD 3 ]--------------------------------------- gid | 3 objectid | 1003 owner | WIGGINS JOELLA R shapestare | 10364.022102999999333 shapestlen | 427.298593999999980 -[ RECORD 4 ]---------------------------------------

-

Create permanent table address_spatial.census_metro_areas

CREATE TABLE address_spatial.census_metro_areas ( gid integer NOT NULL, name character varying(62), geom geometry(MultiPolygon), CONSTRAINT pk_census_metro_area_pk PRIMARY KEY (gid) );

CREATE INDEX idx_census_metro_area ON address_spatial.census_metro_areas USING gist(geom);

-

Copy tmp table into permanent table

INSERT INTO address_spatial.census_metro_areas (gid, name, geom) SELECT gid, name, geom FROM address_spatial.census_metro_area_tmp;

-

Test

SELECT name FROM address_spatial.census_metro_areas WHERE ST_Intersects( ST_MakePoint( -94.5867908690, 39.0903343205), geom);

-

Add column to attributes table city_address_attributes

\c address_api ALTER TABLE census_attributes ADD COLUMN metro_areas character varying(62);

-

Create query for GEOJSON based off of http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type , ST_AsGeoJSON(lg.geom)::json As geometry , row_to_json(lp) As properties FROM address_spatial.census_metro_areas As lg INNER JOIN (SELECT gid, name FROM address_spatial.census_metro_areas) As lp ON lg.gid = lp.gid ORDER BY lg.name) As f ) As fc;

-

Update index.php, the api program

Backup

pg_dump address_api | gzip > address_api-20150910.sql.gz pg_dump census | gzip > census-20150910.sql.gz pg_dump code4kc | gzip > census-20150910.sql.gz

-

Add field to Model CensusAttributes.php

  • Add field to Address.php queries.
  • Create Load and run
  • Update webroot/index.php if nessary

— Reply to this email directly or view it on GitHub https://github.com/codeforkansascity/address-api/issues/40.

rlh-aagis commented 9 years ago

Paul,

Are these available addresses now through the Address API along with the addresses you had before from KCMO?

Thanks, Ron

On Thu, Sep 24, 2015 at 10:41 PM, ronh allaboutgis < ronh.allaboutgis@gmail.com> wrote:

This is good news Paul! On Sep 24, 2015 10:38 PM, "Paul D Barham" notifications@github.com wrote:

-

Get shape file cb_2014_us_cbsa_500k.zip - Had error

Load shape file into temporary table address_spatial.census_metro_area_tmp

shp2pgsql -g geom \ -I /var/wwwsites/dev-api.codeforkc.org/data/jackson_county_mo/set-2/Parcels_andAddresses::_Jackson_County_MO.shp \ address_spatial.jackson_cnt_mo_2_tmp | psql -d code4kc

this results in

                                   Table "address_spatial.jackson_cnt_mo_2_tmp"

Column | Type | Modifiers ------------+------------------------+------------------------------------------------------------------------------------ gid | integer | not null default nextval('address_spatial.jackson_cnt_mo_2_tmp_gid_seq'::regclass) objectid | numeric(10,0) | owner | character varying(80) | shapestare | numeric | shapestlen | numeric | shape_star | numeric | shape_st_1 | numeric | shape_stle | numeric | shape_st_2 | numeric | geom | geometry(MultiPolygon) | Indexes: "jackson_cnt_mo_2_tmp_pkey" PRIMARY KEY, btree (gid) "jackson_cnt_mo_2_tmp_geom_gist" gist (geom) ````

select gid, objectid, owner, shapestare, shapestlen from address_spatial.jackson_cnt_mo_2_tmp limit 10; -[ RECORD 1 ]--------------------------------------- gid | 1 objectid | 1001 owner | RICE PAUL & JENNIFER shapestare | 9368.024019000000408 shapestlen | 415.248697999999990 -[ RECORD 2 ]--------------------------------------- gid | 2 objectid | 1002 owner | ALBERT JERRY DEAN & MARGARET R-TRUSTEE& shapestare | 10449.461418999999296 shapestlen | 424.035738999999978 -[ RECORD 3 ]--------------------------------------- gid | 3 objectid | 1003 owner | WIGGINS JOELLA R shapestare | 10364.022102999999333 shapestlen | 427.298593999999980 -[ RECORD 4 ]---------------------------------------

-

Create permanent table address_spatial.census_metro_areas

CREATE TABLE address_spatial.census_metro_areas ( gid integer NOT NULL, name character varying(62), geom geometry(MultiPolygon), CONSTRAINT pk_census_metro_area_pk PRIMARY KEY (gid) );

CREATE INDEX idx_census_metro_area ON address_spatial.census_metro_areas USING gist(geom);

-

Copy tmp table into permanent table

INSERT INTO address_spatial.census_metro_areas (gid, name, geom) SELECT gid, name, geom FROM address_spatial.census_metro_area_tmp;

-

Test

SELECT name FROM address_spatial.census_metro_areas WHERE ST_Intersects( ST_MakePoint( -94.5867908690, 39.0903343205), geom);

-

Add column to attributes table city_address_attributes

\c address_api ALTER TABLE census_attributes ADD COLUMN metro_areas character varying(62);

-

Create query for GEOJSON based off of http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type , ST_AsGeoJSON(lg.geom)::json As geometry , row_to_json(lp) As properties FROM address_spatial.census_metro_areas As lg INNER JOIN (SELECT gid, name FROM address_spatial.census_metro_areas) As lp ON lg.gid = lp.gid ORDER BY lg.name) As f ) As fc;

-

Update index.php, the api program

Backup

pg_dump address_api | gzip > address_api-20150910.sql.gz pg_dump census | gzip > census-20150910.sql.gz pg_dump code4kc | gzip > census-20150910.sql.gz

-

Add field to Model CensusAttributes.php

  • Add field to Address.php queries.
  • Create Load and run
  • Update webroot/index.php if nessary

— Reply to this email directly or view it on GitHub https://github.com/codeforkansascity/address-api/issues/40.

zmon commented 9 years ago

Not yet, but soon

Sent from my iPhone

On Sep 25, 2015, at 5:41 AM, Ron House notifications@github.com wrote:

Paul,

Are these available addresses now through the Address API along with the addresses you had before from KCMO?

Thanks, Ron

On Thu, Sep 24, 2015 at 10:41 PM, ronh allaboutgis < ronh.allaboutgis@gmail.com> wrote:

This is good news Paul! On Sep 24, 2015 10:38 PM, "Paul D Barham" notifications@github.com wrote:

Get shape file cb_2014_us_cbsa_500k.zip - Had error

Load shape file into temporary table address_spatial.census_metro_area_tmp

shp2pgsql -g geom \ -I /var/wwwsites/dev-api.codeforkc.org/data/jackson_county_mo/set-2/Parcels_andAddresses::_Jackson_County_MO.shp \ address_spatial.jackson_cnt_mo_2_tmp | psql -d code4kc

this results in

Table "address_spatial.jackson_cnt_mo_2_tmp" Column | Type | Modifiers ------------+------------------------+------------------------------------------------------------------------------------ gid | integer | not null default nextval('address_spatial.jackson_cnt_mo_2_tmp_gid_seq'::regclass) objectid | numeric(10,0) | owner | character varying(80) | shapestare | numeric | shapestlen | numeric | shape_star | numeric | shape_st_1 | numeric | shape_stle | numeric | shape_st_2 | numeric | geom | geometry(MultiPolygon) | Indexes: "jackson_cnt_mo_2_tmp_pkey" PRIMARY KEY, btree (gid) "jackson_cnt_mo_2_tmp_geom_gist" gist (geom) ````

select gid, objectid, owner, shapestare, shapestlen from address_spatial.jackson_cnt_mo_2_tmp limit 10; -[ RECORD 1 ]--------------------------------------- gid | 1 objectid | 1001 owner | RICE PAUL & JENNIFER shapestare | 9368.024019000000408 shapestlen | 415.248697999999990 -[ RECORD 2 ]--------------------------------------- gid | 2 objectid | 1002 owner | ALBERT JERRY DEAN & MARGARET R-TRUSTEE& shapestare | 10449.461418999999296 shapestlen | 424.035738999999978 -[ RECORD 3 ]--------------------------------------- gid | 3 objectid | 1003 owner | WIGGINS JOELLA R shapestare | 10364.022102999999333 shapestlen | 427.298593999999980 -[ RECORD 4 ]---------------------------------------

Create permanent table address_spatial.census_metro_areas

CREATE TABLE address_spatial.census_metro_areas ( gid integer NOT NULL, name character varying(62), geom geometry(MultiPolygon), CONSTRAINT pk_census_metro_area_pk PRIMARY KEY (gid) );

CREATE INDEX idx_census_metro_area ON address_spatial.census_metro_areas USING gist(geom);

Copy tmp table into permanent table

INSERT INTO address_spatial.census_metro_areas (gid, name, geom) SELECT gid, name, geom FROM address_spatial.census_metro_area_tmp;

Test

SELECT name FROM address_spatial.census_metro_areas WHERE ST_Intersects( ST_MakePoint( -94.5867908690, 39.0903343205), geom);

Add column to attributes table city_address_attributes

\c address_api ALTER TABLE census_attributes ADD COLUMN metro_areas character varying(62);

Create query for GEOJSON based off of http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type , ST_AsGeoJSON(lg.geom)::json As geometry , row_to_json(lp) As properties FROM address_spatial.census_metro_areas As lg INNER JOIN (SELECT gid, name FROM address_spatial.census_metro_areas) As lp ON lg.gid = lp.gid ORDER BY lg.name) As f ) As fc;

Update index.php, the api program

Backup

pg_dump address_api | gzip > address_api-20150910.sql.gz pg_dump census | gzip > census-20150910.sql.gz pg_dump code4kc | gzip > census-20150910.sql.gz

Add field to Model CensusAttributes.php

  • Add field to Address.php queries.
  • Create Load and run
  • Update webroot/index.php if nessary

— Reply to this email directly or view it on GitHub https://github.com/codeforkansascity/address-api/issues/40.

— Reply to this email directly or view it on GitHub.