def upload_shape(shapepath):
"""
Upload file to database
:param shapepath: path to the shapefile
:return: None
"""
conn = None
cur = None
try:
# first create the sqlstring with inserts
# call PGSQL2SHP with some parameters, -s 4326 to set lat/lon srid, -I to create a spatial index on the geometry column
params = [
settings.SHP2PGSQL, "-s", "4326", "-I", shapepath,
settings.STATES_TABLE_NAME
]
sqlstring, info = utils.run_tool(params)
if not sqlstring:
raise Exception("cannot upload file to database")
#then use the sqlstring
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
cur.execute(sqlstring)
conn.commit()
finally:
if cur:
cur.close()
if conn:
conn.close()
def check_table(schemaname=settings.DEFAULT_SCHEMA, tablename=settings.STATES):
"""
Check if the table exercise.states is already in the database
:param schemaname:
:param tablename:
:return: True if table exists otherwise False
"""
conn = None
cur = None
try:
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
cur.execute("""SELECT to_regclass('%s.%s');""",
(AsIs(schemaname), AsIs(tablename)))
result = cur.fetchone()[0]
return (True if result else False)
except Exception as e:
raise Exception(e)
finally:
if conn: conn = None
if cur: cur = None
def add_all_points(map, marker=settings.MARKER, color=settings.MARKER_COLOR):
"""
Add all the points to the map
:param map: a matplotlib map object
:param marker: a font-awesome marker
:return:
"""
conn = None
curr = None
try:
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
# if the point is inside this will return (True,) otherwise None
cur.execute("""select lon, lat, label,size from %s""",
(AsIs(settings.BOOKMARKS_TABLE_NAME), ))
result = cur.fetchall()
#iterate and add point
for rs in result:
s = get_class_size(rs[3])
folium.map.Marker(
location=[rs[1], rs[0]],
popup=rs[2],
icon=DivIcon(
# icon_size=(150,36),
icon_anchor=(0, 0),
html='<i class="fa ' + marker + ' ' + s +
'" style="color:' + color +
'" aria-hidden="true"></i>')).add_to(map)
except Exception as e:
raise Exception(e)
finally:
if cur: cur = None
if conn: conn = None
def add_all_points(plt,map, sizemult=3):
"""
Add all the points to the map
:param plt: a matplotlib plot object
:param map: a matplotlib map object
:param sizemult: multiplier to scale the marker size
:return:
"""
conn = None
curr = None
try:
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
# if the point is inside this will return (True,) otherwise None
cur.execute("""select lon, lat, label,size from %s""", (AsIs(settings.BOOKMARKS_TABLE_NAME),))
result = cur.fetchall()
#iterate and add point
for rs in result:
x_, y_ = map(rs[0], rs[1])
if rs[2]:
plt.text(x_, y_, rs[2], fontsize=8, fontweight='bold',
ha='center', va='bottom', color='k')
map.plot(x_, y_, "r*", markersize=rs[3]*sizemult)
return plt
except Exception as e:
raise Exception(e)
finally:
if cur: cur = None
if conn: conn = None
def get_geojson():
"""
Download geojson from the database
:return: None
"""
# check the file was already downloaded
global GEOJSON
if GEOJSON: return GEOJSON
conn = None
cur = None
try:
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
cur.execute(
"""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 %s As lg INNER JOIN (SELECT gid,name FROM %s) As lp
ON lg.gid = lp.gid ) As f) As fc;""", (AsIs(
settings.STATES_TABLE_NAME), AsIs(settings.STATES_TABLE_NAME)))
result = cur.fetchone()[0]
#print(result)
#make the result global
GEOJSON = result
except Exception as e:
raise Exception(e)
finally:
if conn: conn = None
if cur: cur = None
def upload_point(x, y, label=""):
"""
Check the user input for a point, if inside USA, if OK add it to the database
Crop nummber to 4 digits, define the marker size based on the bigger number of digits
:param x: point longitude (wgs84) as string
:param y: point latitude (wgs84) as string
:param label: point label
:return: ("longitude","latitude", size) ; coordinates are cropped to 4 decimal digits, size will be the bookmark size
"""
conn = None
cur = None
try:
# check the point is inside the usa, both point and states must be WGS84
conn = utils.pgconnect(**settings.DEFAULT_CONNECTION)
cur = conn.cursor()
#if the point is inside this will return (True,) otherwise None
cur.execute(
"""select result from
(select st_contains(s.geom,ST_GeomFromText('POINT(%s %s)', 4326)) as result
from %s as s) as subquery
where result is true""",
(AsIs(x), AsIs(y), AsIs(settings.STATES_TABLE_NAME)))
result = cur.fetchone()
#print(result)
if result: # if result is not None
#check numbers size, crop to 4 digits, define the marker size
# size symbol
size = None
# store number of decimal digits
lx = 0
ly = 0
# convert numbers to string
#x = str(x);y = str(y)
if ',' in x or ',' in y:
raise Exception("decimal numbers should not contain ','")
# check the number of decimal digits and crop to 4
if '.' in x: # do only for float number
lx = len(x.split('.')[1]) # get decimals
if lx > 4: # crop size to 4
x = x[:(4 - lx)]
lx = 4
if '.' in y: # do only for float number
ly = len(y.split('.')[1])
if ly > 4:
y = y[:(4 - ly)]
ly = 4
# select a symbol size according
# for the size take the bigger number of digits of the two numbers
ndigits = max([lx, ly])
if ndigits == 0:
size = 5
elif ndigits == 1:
size = 4
elif ndigits == 2:
size = 3
elif ndigits == 3:
size = 2
elif ndigits == 4:
size = 1
#upload to database
cur.execute(
"""INSERT INTO %s(lat,lon,label,size) VALUES (%s,%s,%s,%s) RETURNING id""",
(AsIs(settings.BOOKMARKS_TABLE_NAME), y, x, label, size))
#id = cur.fetchone()[0]
#print(id)
cur.execute(
"""UPDATE %s SET geom = ST_PointFromText('POINT(' || lon || ' ' || lat || ')', 4326)""",
(AsIs(settings.BOOKMARKS_TABLE_NAME), ))
conn.commit()
else:
raise Exception("the point is not inside USA")
except Exception as e:
raise Exception(e)
else:
return x, y, size #return the cropped coordinates and marker size
finally:
if cur: cur = None
if conn: conn = None