Skip to content

Instantly share code, notes, and snippets.

@tomasoak

tomasoak/prodes2postgis.py

Created March 31, 2020 16:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tomasoak/3389f9a2041495e4372d51e0ce5176e6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tomasoak/3389f9a2041495e4372d51e0ce5176e6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @fjcerignoni fjcerignoni created this gist Mar 31, 2020.
    51 changes: 51 additions & 0 deletions prodes2postgis.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,51 @@
    ### Testado utilizando python 3.7

    ## requirements:
    ## pip intall psycopg2 sqlalchemy pandas

    import psycopg2
    from sqlalchemy import create_engine
    import pandas as pd

    from datetime import date
    from requests import Request

    ## EDITE SUA CONEXÃO ABAIXO
    engine = create_engine('postgresql://user:password@host:port/database')

    #PRODES AMZ:
    url_prodes='http://terrabrasilis.dpi.inpe.br/geoserver/prodes-amz/wfs'
    layer='prodes-amz:yearly_deforestation_2008_2018_biome'

    ## PRODES CERRADO:
    #url_prodes='http://terrabrasilis.dpi.inpe.br/geoserver/prodes-cerrado/wfs'
    #layer='prodes-cerrado:prodes_cerrado_2000_2018_uf_mun'

    init_year = '2008'
    final_year = '2018'

    CQL_FILTER="ano BETWEEN '{}' AND '{}'".format(init_year,final_year)
    ## PARA FILTRAR OS DADOS POR UM ESTADO ESPECÍFICO UTILIZE A COLUNA 'uf' NO FILTRO ACIMA: "AND uf='AC'"

    params = dict(service='WFS', version='2.0.0', request='GetFeature', srsName='EPSG:4674', typeName=layer, CQL_FILTER=CQL_FILTER, outputFormat='csv')
    csv_prodes = Request('GET', url_prodes, params=params).prepare().url

    try:
    ## leitura do csv requisitado como um dataframe do pandas
    df_prodes = pd.read_csv(csv_prodes)
    except Exception as error:
    print(error)

    ## testa se o dataframe está vazio
    if not df_prodes.empty:
    try:
    ## faz a inserção dos dados do deter no postgreSQL, nome da tabela 'public.desm_deter', caso necessite criar a tabela
    ## em um outro schema, utilizar o parâmetro 'schema' df_deter.to_sql('desm_deter', schema='meu_schema', engine)
    df_prodes.to_sql('desm_prodes', engine, if_exists='append')

    ## O campo de geometria virá como tipo texto (WKT), é necessário converter para o tipo geometry utilizando:
    ## ST_SetSRID(ST_GeomFromText(geom),4674) AS geom
    except Exception as error:
    print(error)
    else:
    print("The dataframe is empty")