def dat_crime(fpath=None): # Step Zero: Create dat_crime table raw_crime(fpath=fpath) dedupe_crime() src_crime() src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) dat_crime_table = crime_table('dat_chicago_crimes_all', Base.metadata) dat_crime_table.append_column(Column('chicago_crimes_all_row_id', Integer, primary_key=True)) dat_crime_table.append_column(Column('start_date', TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))) dat_crime_table.append_column(Column('end_date', TIMESTAMP, server_default=text('NULL'))) dat_crime_table.append_column(Column('current_flag', Boolean, server_default=text('TRUE'))) dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date')) dat_crime_table.create(bind=engine, checkfirst=True) new_cols = ['start_date', 'end_date', 'current_flag', 'chicago_crimes_all_row_id'] dat_ins = dat_crime_table.insert()\ .from_select( [c for c in dat_crime_table.columns.keys() if c not in new_cols], select([c for c in src_crime_table.columns]) ) conn = engine.contextual_connect() res = conn.execute(dat_ins) cols = crime_master_cols(dat_crime_table) master_ins = MasterTable.insert()\ .from_select( [c for c in MasterTable.columns.keys() if c != 'master_row_id'], select(cols)\ .select_from(dat_crime_table) ) conn = engine.contextual_connect() res = conn.execute(master_ins) return 'DAT crime created'
def dat_crime(fpath=None): # Step Zero: Create dat_crime table raw_crime(fpath=fpath) dedupe_crime() src_crime() src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) dat_crime_table = crime_table('dat_chicago_crimes_all', Base.metadata) dat_crime_table.append_column(Column('chicago_crimes_all_row_id', Integer, primary_key=True)) dat_crime_table.append_column(Column('start_date', TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'))) dat_crime_table.append_column(Column('end_date', TIMESTAMP, server_default=text('NULL'))) dat_crime_table.append_column(Column('current_flag', Boolean, server_default=text('TRUE'))) dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date')) dat_crime_table.create(bind=engine, checkfirst=True) new_cols = ['start_date', 'end_date', 'current_flag', 'chicago_crimes_all_row_id'] dat_ins = dat_crime_table.insert()\ .from_select( [c for c in dat_crime_table.columns.keys() if c not in new_cols], select([c for c in src_crime_table.columns]) ) conn = engine.contextual_connect() res = conn.execute(dat_ins) cols = crime_master_cols(dat_crime_table) master_ins = MasterTable.insert()\ .from_select( [c for c in MasterTable.columns.keys() if c != 'master_row_id'], select(cols)\ .select_from(dat_crime_table) ) conn = engine.contextual_connect() res = conn.execute(master_ins) cleanup_temp_tables() return 'DAT crime created'
def chg_crime(): # Step Seven: Find updates dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata, Column('id', Integer, primary_key=True), extend_existing=True) chg_crime_table.drop(bind=engine, checkfirst=True) chg_crime_table.create(bind=engine) src_cols = [c for c in src_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] dat_cols = [c for c in dat_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] and_args = [] for s, d in zip(src_cols, dat_cols): ors = or_(s != None, d != None) ands = and_(ors, s != d) and_args.append(ands) ins = chg_crime_table.insert()\ .from_select( ['id'], select([src_crime_table.c.id])\ .select_from(src_crime_table.join(dat_crime_table, src_crime_table.c.id == dat_crime_table.c.id))\ .where(or_( and_(dat_crime_table.c.current_flag == True, and_(or_(src_crime_table.c.id != None, dat_crime_table.c.id != None), src_crime_table.c.id != dat_crime_table.c.id)), *and_args)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Changes found'
def new_crime(): # Step Four: Find New Crimes dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) new_crime_table = Table('new_chicago_crimes_all', Base.metadata, Column('id', Integer, primary_key=True), extend_existing=True) new_crime_table.drop(bind=engine, checkfirst=True) new_crime_table.create(bind=engine) ins = new_crime_table.insert()\ .from_select( ['id'], select([src_crime_table.c.id])\ .select_from(src_crime_table.join(dat_crime_table, src_crime_table.c.id == dat_crime_table.c.id, isouter=True))\ .where(dat_crime_table.c.chicago_crimes_all_row_id == None) ) conn = engine.contextual_connect() try: conn.execute(ins) return 'New records found' except TypeError: # No new records return None
def update_dat_crimes(): # Step Five: Update Main Crime table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) try: new_crime_table = Table('new_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) except NoSuchTableError: return None excluded_cols = ['end_date', 'current_flag', 'chicago_crimes_all_row_id'] dat_cols = [c for c in dat_crime_table.columns.keys() if c not in excluded_cols] excluded_cols.append('start_date') src_cols = [c for c in src_crime_table.columns if c.name not in excluded_cols] src_cols.append(text("'%s' AS start_date" % datetime.now().strftime('%Y-%m-%d'))) ins = dat_crime_table.insert()\ .from_select( dat_cols, select(src_cols)\ .select_from(src_crime_table.join(new_crime_table, src_crime_table.c.id == new_crime_table.c.id)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Crime Table updated'
def new_crime(): # Step Four: Find New Crimes dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) new_crime_table = Table('new_chicago_crimes_all', Base.metadata, Column('id', Integer, primary_key=True), extend_existing=True) new_crime_table.drop(bind=engine, checkfirst=True) new_crime_table.create(bind=engine) ins = new_crime_table.insert()\ .from_select( ['id'], select([src_crime_table.c.id])\ .select_from(src_crime_table.join(dat_crime_table, src_crime_table.c.id == dat_crime_table.c.id, isouter=True))\ .where(dat_crime_table.c.chicago_crimes_all_row_id == None) ) conn = engine.contextual_connect() try: conn.execute(ins) return 'New records found' except TypeError: # No new records return None
def update_dat_crimes(): # Step Five: Update Main Crime table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) try: new_crime_table = Table('new_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) except NoSuchTableError: return None excluded_cols = ['end_date', 'current_flag', 'chicago_crimes_all_row_id'] dat_cols = [c for c in dat_crime_table.columns.keys() if c not in excluded_cols] excluded_cols.append('start_date') src_cols = [c for c in src_crime_table.columns if c.name not in excluded_cols] src_cols.append(text("'%s' AS start_date" % datetime.now().strftime('%Y-%m-%d'))) ins = dat_crime_table.insert()\ .from_select( dat_cols, select(src_cols)\ .select_from(src_crime_table.join(new_crime_table, src_crime_table.c.id == new_crime_table.c.id)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Crime Table updated'
def chg_crime(): # Step Seven: Find updates dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = Table('src_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata, Column('id', Integer, primary_key=True), extend_existing=True) chg_crime_table.drop(bind=engine, checkfirst=True) chg_crime_table.create(bind=engine) src_cols = [c for c in src_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] dat_cols = [c for c in dat_crime_table.columns if c.name not in ['id', 'start_date', 'end_date']] and_args = [] for s, d in zip(src_cols, dat_cols): ors = or_(s != None, d != None) ands = and_(ors, s != d) and_args.append(ands) ins = chg_crime_table.insert()\ .from_select( ['id'], select([src_crime_table.c.id])\ .select_from(src_crime_table.join(dat_crime_table, src_crime_table.c.id == dat_crime_table.c.id))\ .where(or_( and_(dat_crime_table.c.current_flag == True, and_(or_(src_crime_table.c.id != None, dat_crime_table.c.id != None), src_crime_table.c.id != dat_crime_table.c.id)), *and_args)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Changes found'
def update_master_current_flag(): # Step Eight: Update end_date and current_flag in master table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) update = MasterTable.update()\ .values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\ .where(MasterTable.c.dataset_row_id == dat_crime_table.c.chicago_crimes_all_row_id)\ .where(dat_crime_table.c.current_flag==False)\ .where(dat_crime_table.c.end_date==date.today()) conn = engine.contextual_connect() conn.execute(update) return 'Master table current flag updated'
def update_master_current_flag(): # Step Eight: Update end_date and current_flag in master table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) update = MasterTable.update()\ .values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\ .where(MasterTable.c.dataset_row_id == dat_crime_table.c.chicago_crimes_all_row_id)\ .where(dat_crime_table.c.current_flag==False)\ .where(dat_crime_table.c.end_date==date.today()) conn = engine.contextual_connect() conn.execute(update) return 'Master table current flag updated'
def update_crime_current_flag(): # Step Seven: Update end_date and current_flag in crime table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) update = dat_crime_table.update()\ .values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\ .where(dat_crime_table.c.id==chg_crime_table.c.id)\ .where(dat_crime_table.c.current_flag == True) conn = engine.contextual_connect() conn.execute(update) return 'Crime table current flag updated'
def update_crime_current_flag(): # Step Seven: Update end_date and current_flag in crime table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) chg_crime_table = Table('chg_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) update = dat_crime_table.update()\ .values(current_flag=False, end_date=datetime.now().strftime('%Y-%m-%d'))\ .where(dat_crime_table.c.id==chg_crime_table.c.id)\ .where(dat_crime_table.c.current_flag == True) conn = engine.contextual_connect() conn.execute(update) return 'Crime table current flag updated'
def sf_crime(fpath=None, crime_type='violent'): #raw_crime = sf_raw_crime(fpath=fpath) # Assume for now there's no duplicate in the raw data, which means we don't # - dedupe_crime() # - and don't create src_crime() raw_crime_table = Table('raw_sf_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) if crime_type == 'violent': categories = ['ASSAULT', 'ROBBERY', 'SEX OFFENSES, FORCIBLE'] elif crime_type == 'property': categories = ['LARCENY/THEFT', 'VEHICLE THEFT', 'BURGLARY', 'STOLEN PROPERTY',\ 'ARSON', 'VANDALISM'] # Create table "dat_sf_crimes_all", that contains additional fields needed # by Plenario, in addition to the raw data crime_table = sf_crime_table('sf_{0}_crimes'.format(crime_type), Base.metadata) # Add geom column crime_table.append_column(Column('geom', Geometry('POINT', srid=4326))) # Add obs_date column crime_table.append_column(Column('obs_date', DateTime)) # Add row_id column crime_table.append_column(Column('row_id', Integer, primary_key=True)) # Constrain (id, start_date) to be unique (?) # dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date')) crime_table.drop(bind=engine, checkfirst=True) crime_table.create(bind=engine) new_cols = ['row_id'] # Insert data from raw_crime_table (to be src_crime_table when we'll check # for duplicates) dat_ins = crime_table.insert()\ .from_select( [c for c in crime_table.columns.keys() if c not in new_cols], select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'] + [ func.ST_SetSRID( func.ST_MakePoint(raw_crime_table.c['longitude'], raw_crime_table.c['latitude']), 4326) ] + \ [ raw_crime_table.c['date'].label('obs_date') ])\ .where(raw_crime_table.c['category'].in_(categories)) ) conn = engine.contextual_connect() res = conn.execute(dat_ins) return 'Table sf_{0}_crime created'.format(crime_type)
def dedupe_crime(): # Step Two: Find duplicate records by ID raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata, Column('dup_row_id', Integer, primary_key=True), extend_existing=True) dedupe_crime_table.drop(bind=engine, checkfirst=True) dedupe_crime_table.create(bind=engine) ins = dedupe_crime_table.insert()\ .from_select( ['dup_row_id'], select([func.max(raw_crime_table.c.dup_row_id)])\ .group_by(raw_crime_table.c.id) ) conn = engine.contextual_connect() res = conn.execute(ins) return 'Raw crime deduplicated'
def src_crime(): # Step Three: Create New table with unique ids raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = crime_table('src_chicago_crimes_all', Base.metadata) src_crime_table.drop(bind=engine, checkfirst=True) src_crime_table.create(bind=engine) ins = src_crime_table.insert()\ .from_select( src_crime_table.columns.keys(), select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'])\ .where(raw_crime_table.c.dup_row_id == dedupe_crime_table.c.dup_row_id) ) conn = engine.contextual_connect() conn.execute(ins) return 'Source table created'
def src_crime(): # Step Three: Create New table with unique ids raw_crime_table = Table('raw_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) dedupe_crime_table = Table('dedup_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) src_crime_table = crime_table('src_chicago_crimes_all', Base.metadata) src_crime_table.drop(bind=engine, checkfirst=True) src_crime_table.create(bind=engine) ins = src_crime_table.insert()\ .from_select( src_crime_table.columns.keys(), select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'])\ .where(raw_crime_table.c.dup_row_id == dedupe_crime_table.c.dup_row_id) ) conn = engine.contextual_connect() conn.execute(ins) return 'Source table created'
def add_dataset_meta(name, file_name='', human_name='', description='', val_attr='', count_q=False, area_q=False, dist_q=False, temp_q=False, weighted_q=False, access_q=False, voronoi=False, duration='interval', demo=False): """ Add infotmation about a dataset in the meta table """ if human_name == '': human_name = name meta_table = Table('sf_meta', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) row = {'table_name': name, 'file_name': file_name, 'human_name': human_name,'description': description, 'last_update': func.current_timestamp(), 'val_attr': val_attr, 'count_q': count_q, 'area_q': area_q, 'dist_q': dist_q, 'temp_q': temp_q, 'weighted_q': weighted_q, 'access_q': access_q, 'voronoi': voronoi, 'duration': duration, 'demo': demo} ins = meta_table.insert(row) conn = engine.contextual_connect() conn.execute(ins) return 'Meta information for {0} inserted.'.format(name)
def add_dataset_meta(name, file_name='', human_name='', description='', val_attr='', count_q=False, area_q=False, dist_q=False, temp_q=False, weighted_q=False, access_q=False, voronoi=False, duration='interval', demo=False): """ Add infotmation about a dataset in the meta table """ if human_name == '': human_name = name meta_table = Table('sf_meta', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) row = { 'table_name': name, 'file_name': file_name, 'human_name': human_name, 'description': description, 'last_update': func.current_timestamp(), 'val_attr': val_attr, 'count_q': count_q, 'area_q': area_q, 'dist_q': dist_q, 'temp_q': temp_q, 'weighted_q': weighted_q, 'access_q': access_q, 'voronoi': voronoi, 'duration': duration, 'demo': demo } ins = meta_table.insert(row) conn = engine.contextual_connect() conn.execute(ins) return 'Meta information for {0} inserted.'.format(name)
def sf_crime(fpath=None, crime_type='violent'): #raw_crime = sf_raw_crime(fpath=fpath) # Assume for now there's no duplicate in the raw data, which means we don't # - dedupe_crime() # - and don't create src_crime() raw_crime_table = Table('raw_sf_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) if crime_type == 'violent': categories = ['ASSAULT', 'ROBBERY', 'SEX OFFENSES, FORCIBLE'] elif crime_type == 'property': categories = ['LARCENY/THEFT', 'VEHICLE THEFT', 'BURGLARY', 'STOLEN PROPERTY',\ 'ARSON', 'VANDALISM'] # Create table "dat_sf_crimes_all", that contains additional fields needed # by Plenario, in addition to the raw data crime_table = sf_crime_table('sf_{0}_crimes'.format(crime_type), Base.metadata) # Add geom column crime_table.append_column(Column('geom', Geometry('POINT', srid=4326))) # Add obs_date column crime_table.append_column(Column('obs_date', DateTime)) # Add row_id column crime_table.append_column(Column('row_id', Integer, primary_key=True)) # Constrain (id, start_date) to be unique (?) # dat_crime_table.append_constraint(UniqueConstraint('id', 'start_date')) crime_table.drop(bind=engine, checkfirst=True) crime_table.create(bind=engine) new_cols = ['row_id'] # Insert data from raw_crime_table (to be src_crime_table when we'll check # for duplicates) dat_ins = crime_table.insert()\ .from_select( [c for c in crime_table.columns.keys() if c not in new_cols], select([c for c in raw_crime_table.columns if c.name != 'dup_row_id'] + [ func.ST_SetSRID( func.ST_MakePoint(raw_crime_table.c['longitude'], raw_crime_table.c['latitude']), 4326) ] + \ [ raw_crime_table.c['date'].label('obs_date') ])\ .where(raw_crime_table.c['category'].in_(categories)) ) conn = engine.contextual_connect() res = conn.execute(dat_ins) return 'Table sf_{0}_crime created'.format(crime_type)
def update_master(): # Step Six: Update Master table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) try: new_crime_table = Table('new_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) except NoSuchTableError: return None cols = crime_master_cols(dat_crime_table) ins = MasterTable.insert()\ .from_select( [c for c in MasterTable.columns.keys() if c != 'master_row_id'], select(cols)\ .select_from(dat_crime_table.join(new_crime_table, dat_crime_table.c.id == new_crime_table.c.id)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Master updated'
def update_master(): # Step Six: Update Master table dat_crime_table = Table('dat_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) try: new_crime_table = Table('new_chicago_crimes_all', Base.metadata, autoload=True, autoload_with=engine, extend_existing=True) except NoSuchTableError: return None cols = crime_master_cols(dat_crime_table) ins = MasterTable.insert()\ .from_select( [c for c in MasterTable.columns.keys() if c != 'master_row_id'], select(cols)\ .select_from(dat_crime_table.join(new_crime_table, dat_crime_table.c.id == new_crime_table.c.id)) ) conn = engine.contextual_connect() conn.execute(ins) return 'Master updated'
def import_shapefile_timed(fpath, name, force_multipoly=False, proj=4326, voronoi=False, duration='interval', start_date=None, end_date=None, obs_date_field='obs_date'): """Import a shapefile into the PostGIS database Keyword arguments: fpath -- path to a zipfile to be extracted name -- name given to the newly created table force_multipoly -- enforce that the gemoetries are multipolygons proj -- source projection spec (EPSG code or Proj$ string) voronoi -- compute voronoi triangulations of points duration -- 'interval' or 'event' start_date -- initial date of shapes life (works with duration = 'interval') end_date -- final date of shapes life (works with duration = 'interval') obs_date_field -- where to find event time info (works with duration = 'event') """ # Open the shapefile with fiona. with fiona.open('/', vfs='zip://{0}'.format(fpath)) as shp: shp_table = shp2table(name, Base.metadata, shp.schema, force_multipoly=force_multipoly) shp_table.drop(bind=engine, checkfirst=True) shp_table.append_column(Column('row_id', Integer, primary_key=True)) if duration == 'interval': shp_table.append_column(Column('start_date', DateTime,\ default=datetime.min)) shp_table.append_column(Column('end_date', DateTime,\ default=datetime.max)) else: shp_table.append_column(Column('obs_date', DateTime,\ default=datetime.now())) # If the geometry is not "point", append a centroid column if shp.schema['geometry'].lower() != 'point': shp_table.append_column(Column('centroid', Geometry('POINT', srid=4326))) # Add a column and compute Voronoi triangulation, if required if shp.schema['geometry'].lower() == 'point' and voronoi: pts = [p['geometry']['coordinates'] for p in shp.values()] pts = transform_proj(pts, proj, 4326) pts_map = dict([[str(i), p] for (i, p) in zip(range(len(pts)), pts)]) vor_polygons = VoronoiGeoJson_Polygons(pts_map, BoundingBox='W') vor_polygons = json.loads(vor_polygons) # For matching the polygons to the correct point, we create a # dictionary with _domain_id as keys vor_polygons_dict = dict(zip(range(len(pts)),\ ['POLYGON EMPTY']*len(pts))) for r in vor_polygons: vor_polygons_dict[int(r['properties']['_domain_id'])] =\ shape(r['geometry']).wkt shp_table.append_column(Column('voronoi', Geometry('POLYGON', srid=4326))) shp_table.create(bind=engine) features = [] count = 0 num_shapes = len(shp) for r in shp: # ESRI shapefile don't contemplate multipolygons, i.e. the geometry # type is polygon even if multipolygons are contained. # If and when the 1st multipoly is encountered, the table is # re-initialized. if not force_multipoly and r['geometry']['type'] == 'MultiPolygon': return import_shapefile_timed(fpath, name, force_multipoly=True, proj=proj, voronoi=voronoi, duration=duration, start_date=start_date, end_date=end_date, obs_date_field=obs_date_field) row_dict = dict((k.lower(), v) for k, v in r['properties'].iteritems()) # GeoJSON intermediate representation geom_json = json.loads(str(r['geometry']).replace('\'', '"')\ .replace('(', '[').replace(')', ']')) # If the projection is not long/lat (WGS84 - EPGS:4326), transform. if proj != 4326: geom_json['coordinates'] = transform_proj(geom_json['coordinates'], proj, 4326) # Shapely intermediate representation, used to obtained the WKT geom = shape(geom_json) if force_multipoly and r['geometry']['type'] != 'MultiPolygon': geom = MultiPolygon([geom]) row_dict['geom'] = 'SRID=4326;{0}'.format(geom.wkt) if shp.schema['geometry'].lower() != 'point': row_dict['centroid'] =\ 'SRID=4326;{0}'.format(geom.centroid.wkt) if duration == 'interval': if start_date: row_dict['start_date'] = start_date elif np.random.rand() < 0.33: row_dict['start_date'] = datetime(2004, 03, 04) if end_date: row_dict['end_date'] = end_date elif np.random.rand() < 0.33: row_dict['end_date'] = datetime(2009, 07, 06) else: row_dict['obs_date'] = row_dict[obs_date_field.lower()] del row_dict[obs_date_field] if shp.schema['geometry'].lower() == 'point' and voronoi: row_dict['voronoi'] =\ 'SRID=4326;{0}'.format(vor_polygons_dict[count]) features.append(row_dict) count += 1 #if count > 100: break # Buffer DB writes if not count % 1000 or count == num_shapes: try: ins = shp_table.insert(features) conn = engine.contextual_connect() conn.execute(ins) except SQLAlchemyError as e: print type(e) print e.orig return "Failed." features = [] print "Inserted {0} shapes in dataset {1}".format(count, name) return 'Table {0} created from shapefile'.format(name)
def import_shapefile_timed(fpath, name, force_multipoly=False, proj=4326, voronoi=False, duration='interval', start_date=None, end_date=None, obs_date_field='obs_date'): """Import a shapefile into the PostGIS database Keyword arguments: fpath -- path to a zipfile to be extracted name -- name given to the newly created table force_multipoly -- enforce that the gemoetries are multipolygons proj -- source projection spec (EPSG code or Proj$ string) voronoi -- compute voronoi triangulations of points duration -- 'interval' or 'event' start_date -- initial date of shapes life (works with duration = 'interval') end_date -- final date of shapes life (works with duration = 'interval') obs_date_field -- where to find event time info (works with duration = 'event') """ # Open the shapefile with fiona. with fiona.open('/', vfs='zip://{0}'.format(fpath)) as shp: shp_table = shp2table(name, Base.metadata, shp.schema, force_multipoly=force_multipoly) shp_table.drop(bind=engine, checkfirst=True) shp_table.append_column(Column('row_id', Integer, primary_key=True)) if duration == 'interval': shp_table.append_column(Column('start_date', DateTime,\ default=datetime.min)) shp_table.append_column(Column('end_date', DateTime,\ default=datetime.max)) else: shp_table.append_column(Column('obs_date', DateTime,\ default=datetime.now())) # If the geometry is not "point", append a centroid column if shp.schema['geometry'].lower() != 'point': shp_table.append_column( Column('centroid', Geometry('POINT', srid=4326))) # Add a column and compute Voronoi triangulation, if required if shp.schema['geometry'].lower() == 'point' and voronoi: pts = [p['geometry']['coordinates'] for p in shp.values()] pts = transform_proj(pts, proj, 4326) pts_map = dict([[str(i), p] for (i, p) in zip(range(len(pts)), pts)]) vor_polygons = VoronoiGeoJson_Polygons(pts_map, BoundingBox='W') vor_polygons = json.loads(vor_polygons) # For matching the polygons to the correct point, we create a # dictionary with _domain_id as keys vor_polygons_dict = dict(zip(range(len(pts)),\ ['POLYGON EMPTY']*len(pts))) for r in vor_polygons: vor_polygons_dict[int(r['properties']['_domain_id'])] =\ shape(r['geometry']).wkt shp_table.append_column( Column('voronoi', Geometry('POLYGON', srid=4326))) shp_table.create(bind=engine) features = [] count = 0 num_shapes = len(shp) for r in shp: # ESRI shapefile don't contemplate multipolygons, i.e. the geometry # type is polygon even if multipolygons are contained. # If and when the 1st multipoly is encountered, the table is # re-initialized. if not force_multipoly and r['geometry']['type'] == 'MultiPolygon': return import_shapefile_timed(fpath, name, force_multipoly=True, proj=proj, voronoi=voronoi, duration=duration, start_date=start_date, end_date=end_date, obs_date_field=obs_date_field) row_dict = dict( (k.lower(), v) for k, v in r['properties'].iteritems()) # GeoJSON intermediate representation geom_json = json.loads(str(r['geometry']).replace('\'', '"')\ .replace('(', '[').replace(')', ']')) # If the projection is not long/lat (WGS84 - EPGS:4326), transform. if proj != 4326: geom_json['coordinates'] = transform_proj( geom_json['coordinates'], proj, 4326) # Shapely intermediate representation, used to obtained the WKT geom = shape(geom_json) if force_multipoly and r['geometry']['type'] != 'MultiPolygon': geom = MultiPolygon([geom]) row_dict['geom'] = 'SRID=4326;{0}'.format(geom.wkt) if shp.schema['geometry'].lower() != 'point': row_dict['centroid'] =\ 'SRID=4326;{0}'.format(geom.centroid.wkt) if duration == 'interval': if start_date: row_dict['start_date'] = start_date elif np.random.rand() < 0.33: row_dict['start_date'] = datetime(2004, 03, 04) if end_date: row_dict['end_date'] = end_date elif np.random.rand() < 0.33: row_dict['end_date'] = datetime(2009, 07, 06) else: row_dict['obs_date'] = row_dict[obs_date_field.lower()] del row_dict[obs_date_field] if shp.schema['geometry'].lower() == 'point' and voronoi: row_dict['voronoi'] =\ 'SRID=4326;{0}'.format(vor_polygons_dict[count]) features.append(row_dict) count += 1 #if count > 100: break # Buffer DB writes if not count % 1000 or count == num_shapes: try: ins = shp_table.insert(features) conn = engine.contextual_connect() conn.execute(ins) except SQLAlchemyError as e: print type(e) print e.orig return "Failed." features = [] print "Inserted {0} shapes in dataset {1}".format(count, name) return 'Table {0} created from shapefile'.format(name)