<jupyter_start><jupyter_text>States<jupyter_code>gdf = gpd.read_file('data/states')
gdf = gdf.to_crs(crs)
gdf.head(3)
# drop states table if it already exists, then create states table
query = """
DROP TABLE IF EXISTS states;
CREATE TABLE states (id SERIAL PRIMARY KEY,
                     geoid VARCHAR NOT NULL,
                     stusps VARCHAR NOT NULL,
                     aland BIGINT NOT NULL);
SELECT AddGeometryColumn ('states', 'geom', %s, 'MULTIPOLYGON', 2);
CREATE INDEX states_spatial_idx ON states USING GIST(geom);
"""
data = [srid]
cursor.execute(query, data)
%%time
cursor.execute("DELETE FROM states")

# insert each state into the states table one at a time
for label, row in gdf.iterrows():
    geoid = row['GEOID']
    stusps = row['STUSPS']
    aland = row['ALAND']
    geometry_wkt = row['geometry'].wkt
    
    query = """INSERT INTO states (geoid, stusps, aland, geom) 
               VALUES (%s, %s, %s, ST_Multi(ST_GeomFromText(%s, %s)))"""
    data = (geoid, stusps, aland, geometry_wkt, srid)
    cursor.execute(query, data)
%%time
cursor.execute('VACUUM(FULL, ANALYZE) states;')<jupyter_output>Wall time: 355 ms<jupyter_text>Places<jupyter_code>%%time
input_folder = 'data/places'
gdf = gpd.GeoDataFrame()
for state in os.listdir(input_folder):
    gdf = gdf.append(gpd.read_file('{}/{}'.format(input_folder, state)))

# outside of hawaii, only retain non-CDPs    
mask = (gdf['STATEFP'] == '15') | (~gdf['NAMELSAD'].str.contains('CDP'))
gdf = gdf[mask]
gdf = gdf.to_crs(crs)
print(len(gdf))
gdf[gdf['NAMELSAD'].str.contains('Tucson')]
%%time
# drop places table if it already exists, then create places table
query = """
DROP TABLE IF EXISTS places;
CREATE TABLE places (id SERIAL PRIMARY KEY,
                     geoid VARCHAR NOT NULL,
                     name VARCHAR NOT NULL,
                     aland BIGINT NOT NULL);
SELECT AddGeometryColumn ('places', 'geom', %s, 'MULTIPOLYGON', 2);
CREATE INDEX places_spatial_idx ON places USING GIST(geom);
"""
data = [srid]
cursor.execute(query, data)
%%time
cursor.execute("DELETE FROM places")

# insert each place into the places table one at a time
for label, row in gdf.iterrows():
    geoid = row['GEOID']
    name = row['NAME']
    aland = row['ALAND']
    geometry_wkt = row['geometry'].wkt
    
    query = """INSERT INTO places (geoid, name, aland, geom) 
               VALUES (%s, %s, %s, ST_Multi(ST_GeomFromText(%s, %s)))"""
    data = (geoid, name, aland, geometry_wkt, srid)
    cursor.execute(query, data)
%%time
cursor.execute('VACUUM(FULL, ANALYZE) places;')<jupyter_output>Wall time: 9.99 s<jupyter_text>Urbanized Areas<jupyter_code>gdf = gpd.read_file('data/tl_2017_us_uac10/')
gdf = gdf[~(gdf['NAMELSAD10'].str.contains('Urban Cluster'))]
gdf = gdf.to_crs(crs)
len(gdf)
gdf[gdf['NAMELSAD10'].str.contains('Tucson')]
%%time
# drop urbanized_areas table if it already exists, then create urbanized_areas table
query = """
DROP TABLE IF EXISTS urbanized_areas;
CREATE TABLE urbanized_areas (id SERIAL PRIMARY KEY,
                              geoid VARCHAR NOT NULL,
                              name VARCHAR NOT NULL,
                              aland BIGINT NOT NULL);
SELECT AddGeometryColumn ('urbanized_areas', 'geom', %s, 'MULTIPOLYGON', 2);
CREATE INDEX urbanized_areas_spatial_idx ON urbanized_areas USING GIST(geom);
"""
data = [srid]
cursor.execute(query, data)
%%time
cursor.execute("DELETE FROM urbanized_areas")

# insert each urbanized area into the urbanized_areas table one at a time
for label, row in gdf.iterrows():
    geoid = row['GEOID10']
    name = row['NAMELSAD10']
    aland = row['ALAND10']
    geometry_wkt = row['geometry'].wkt
    
    query = """INSERT INTO urbanized_areas (geoid, name, aland, geom) 
               VALUES (%s, %s, %s, ST_Multi(ST_GeomFromText(%s, %s)))"""
    data = (geoid, name, aland, geometry_wkt, srid)
    cursor.execute(query, data)
%%time
cursor.execute('VACUUM(FULL, ANALYZE) urbanized_areas;')<jupyter_output>Wall time: 12.6 s<jupyter_text>Test<jupyter_code># all footprints within some city
query = """
        SELECT ST_AsText(footprints.geom)
        FROM footprints, places
        WHERE places.name='Tucson'
        AND ST_Intersects(places.geom, footprints.geom);
        """
%%time
cursor.execute(query)
rows = cursor.fetchall()
%%time
gdf = gpd.GeoDataFrame(rows, columns=['geometry'])
gdf['geometry'] = gdf['geometry'].map(lambda x: wkt_loads(x))
print(len(gdf))
# all footprints within some urbanized area
query = """
        SELECT ST_AsText(footprints.geom)
        FROM footprints, urbanized_areas
        WHERE urbanized_areas.name LIKE '%Tucson%AZ%'
        AND ST_Intersects(urbanized_areas.geom, footprints.geom);
        """
%%time
cursor.execute(query)
rows = cursor.fetchall()
%%time
gdf = gpd.GeoDataFrame(rows, columns=['geometry'])
gdf['geometry'] = gdf['geometry'].map(lambda x: wkt_loads(x))
print(len(gdf))
# all footprints within some state
query = """
        SELECT ST_AsText(footprints.geom)
        FROM footprints, states
        WHERE states.stusps='DE'
        AND ST_Intersects(states.geom, footprints.geom);
        """
%%time
cursor.execute(query)
rows = cursor.fetchall()
%%time
gdf = gpd.GeoDataFrame(rows, columns=['geometry'])
gdf['geometry'] = gdf['geometry'].map(lambda x: wkt_loads(x))
print(len(gdf))
cursor.close()
connection.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13743723265761576, 'fraction_numerical': 0.005765296633810675, 'mean_word_length': 3.8669683257918552, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '351142', 'license': 'MIT', 'n_tokens_mistral': 1946, 'n_tokens_neox': 1872, 'n_words': 469, 'path': '05-load-census-geographies-postgis.ipynb', 'repo_name': 'gboeing/ms-bldg-footprints', 'size': '5377'}