python - Converting MSSQL database to GeoJSON format

Question

I'm trying to use Python to access an MSSQL database and then write the results out to a GeoJSON file. So far I have this code, but I can't get a working end part which saves the file to my directory

import pandas as pd
import pyodbc
import geojson
import json
from geojson import Feature, FeatureCollection, Point


conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=LAPTOP;'
                      'Database=WB;'
                      'Trusted_Connection=yes;')


sql_query = pd.read_sql_query(''' 
                              select * from WB.dbo.Transactions
                              '''
                              ,conn) # here, the 'conn' is the variable that contains your database connection information from step 2

df = pd.DataFrame(sql_query)
def df_to_geojson(df, properties, lat='latitude', lon='longitude'):
    geojson = {'type':'FeatureCollection', 'features':[]}
    for _, row in df.iterrows():
        feature = {'type':'Feature',
                   'properties':{},
                   'geometry':{'type':'Point',
                               'coordinates':[]}}
        feature['geometry']['coordinates'] = [row[lon],row[lat]]
        for prop in properties:
            feature['properties'][prop] = row[prop]
        geojson['features'].append(feature)
    return geojson

I've tried the following to save it to an output file, but it throws up an error that GeoJSON is a module not a string - so I changed the above code from geojson to geojson1 and then it comes up that geojson1 isn't defined

with open('C:/Users/Public/Documents/transactions.geojson', 'w') as f:
    f.write(geojson)

标签： pythonsqlpandasgeojson

Answer 1

尝试在将 json.dump(geojson) 写入文件之前添加它。此外，您不应在第 3 行和第 5 行两次导入模块。