def test_ST_Dump(self):
from sqlalchemy.sql import select, func
from geoalchemy2 import WKBElement
lake_id = self._create_one()
lake = session.query(Lake).get(lake_id)
s = select([func.ST_Dump(Lake.__table__.c.geom)])
r1 = session.execute(s).scalar()
ok_(isinstance(r1, str))
s = select([func.ST_Dump(Lake.__table__.c.geom).path])
r2 = session.execute(s).scalar()
ok_(isinstance(r2, list))
eq_(r2, [])
s = select([func.ST_Dump(Lake.__table__.c.geom).geom])
r2 = session.execute(s).scalar()
ok_(isinstance(r2, WKBElement))
eq_(r2.data, lake.geom.data)
r3 = session.execute(func.ST_Dump(lake.geom).geom).scalar()
ok_(isinstance(r3, WKBElement))
eq_(r3.data, lake.geom.data)
r4 = session.query(func.ST_Dump(Lake.geom).geom).scalar()
ok_(isinstance(r4, WKBElement))
eq_(r4.data, lake.geom.data)
r5 = session.query(Lake.geom.ST_Dump().geom).scalar()
ok_(isinstance(r5, WKBElement))
eq_(r5.data, lake.geom.data)
ok_(r2.data == r3.data == r4.data == r5.data)
def test_ST_Dump(self):
from sqlalchemy import func
from sqlalchemy.sql import select
table = _create_geography_table()
s = select([func.ST_Dump(table.c.geom).geom])
eq_sql(s,
'SELECT ST_AsBinary((ST_Dump("table".geom)).geom) AS geom '
'FROM "table"')
def polygon_chunk(self, size=64):
stmt = (session.query(
func.ST_Dump(Place.geom.cast(Geometry())).label('x')).filter_by(
place_id=self.place_id).subquery())
q = session.query(
stmt.c.x.path[1],
func.ST_Area(stmt.c.x.geom.cast(Geography)) / (1000 * 1000),
func.Box2D(stmt.c.x.geom))
for num, area, box2d in q:
chunk_size = utils.calc_chunk_size(area, size=size)
west, south, east, north = map(float, re_box.match(box2d).groups())
for chunk in bbox_chunk((south, north, west, east), chunk_size):
yield chunk
def geom_unfilled(table, output_table_name):
"""
Export rows containing interior rings into another table.
Include the unfilled geometry in the exported table as a new column
named "unfilled".
Parameters
----------
table : sqlalchemy.ext.declarative.DeclarativeMeta
Table ORM class to query for rows containing geometries with
interior rings.
output_table_name : str
Name of exported table. Table is created in the same schema as
the queried table.
Returns
-------
None
"""
# Query rows containing geometries with interior rings.
# Add column for unfilled geometry (outer polygon - polygon).
# TODO: Ignore unfilled areas that are overlapped by another row.
with db.session() as sess:
q = sess.query(
table,
func.ST_Difference(
func.ST_MakePolygon(
func.ST_ExteriorRing(
func.ST_Dump(table.geom).geom
)
),
table.geom
).label('unfilled')
).filter(
func.ST_NRings(table.geom) > 1,
)
# Create new table from query. This table does not contain constraints,
# such as primary keys.
schema = getattr(db.tables, table.__table__.schema)
io.db_to_db(q, output_table_name, schema)
def create_well_bgt_geometry_table():
"""Store well information with bgt information.
creates records for each bgt item close to a well_id.
so each well should have around ~8 items nearby.
"""
conn = engine.connect()
db_model = models.WellBGT
bgt_items = []
bgt_bak_items = []
for key, bgts in WELL_BGT_MAP.items():
point = WELL_POINT_MAP[key]
for bgt_id, geom in bgts:
bgt_geo = geom
new = {
'well_id': key,
'geometrie': point,
}
if geom.startswith('MULTIPOINT'):
new['bgt_bak'] = bgt_geo
bgt_bak_items.append(new)
else:
new['bgt'] = bgt_geo
bgt_items.append(new)
if not bgt_items:
raise ValueError("nothing matched..")
insert_stmt = (db_model.__table__.insert().values(
bgt=func.ST_GeomFromText(bindparam('bgt'), 28992)))
conn.execute(insert_stmt, bgt_items)
if not bgt_bak_items:
raise ValueError("nothing matched..")
insert_stmt = (db_model.__table__.insert().values(bgt_bak=func.ST_Dump(
func.ST_GeomFromText(bindparam('bgt_bak'), 28992)).geom))
conn.execute(insert_stmt, bgt_bak_items)
def get_geometries_from_collection(cls, request, geometry_collection,
type):
try:
session = request.dbsession
with session.no_autoflush:
#return request.dbsession.execute("SELECT public.ST_AsText(public.ST_GeomFromWKB((public.ST_Dump(public.ST_CollectionExtract(public.ST_GeomFromText('GEOMETRYCOLLECTION(LINESTRING(2553935.7897053463 1218112.2472952164,2553995.3326233597 1218167.3796267102),POLYGON((2553987.246548074 1218189.4325593077,2554001.2134053856 1218173.260408736,2554029.147120009 1218196.7835368402,2554013.7100671907 1218212.9556874116,2553987.246548074 1218189.4325593077)),POINT(2553947.5512693985 1218149.7372806321),POINT(2553972.544593009 1218178.4060930088),POINT(2553919.617554775 1218128.419445788),POINT(2554009.2994806715 1218167.3796267102),POINT(2554029.147120009 1218210.7503941518),POINT(2554025.471631243 1218185.021972788),LINESTRING(2554015.1802626974 1218155.6180626582,2554032.8226087755 1218171.0551154765))'),2))).geom))")
settings = request.registry.settings
request.dbsession.execute('SET search_path TO public')
#return request.dbsession.query(func.public.ST_AsText(func.public.ST_GeomFromWKB((func.ST_Dump(func.public.ST_CollectionExtract(func.public.ST_GeomFromText(geometry_collection), type))).geom)).label("geometry")).all()
return session.query(
func.public.ST_AsText(
func.public.ST_GeomFromWKB((func.ST_Dump(
func.public.ST_CollectionExtract(
func.public.ST_GeomFromText(
geometry_collection),
type))).geom)).label("geometry")).all()
request.dbsession.execute('set search_path to ' +
settings['schema_name'])
except Exception as error:
raise error
def generate_grids(config, area=None):
bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326)
grid_obj = config['GRID_OBJ']
resolution = config['RESOLUTION']
epsg = config['EPSG']
try:
grids = session.query(func.ST_Dump(
func.makegrid_2d(bounding_box, resolution, resolution)).geom.label('geom') # self-defined function in Psql
).subquery()
# using the boundary to crop the area
# if config['AREA'] == 'los_angeles':
# grids = session.query(grids.c.geom) \
# .filter(func.ST_Intersects(LosAngelesCountyBoundary.wkb_geometry, grids.c.geom)).subquery()
results = session.query(
func.row_number().over().label('gid'),
func.ST_Centroid(grids.c.geom).label('centroid'),
func.ST_X(func.ST_Centroid(grids.c.geom)).label('lon'),
func.ST_Y(func.ST_Centroid(grids.c.geom)).label('lat'),
grids.c.geom,
func.ST_X(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lon_proj'),
func.ST_Y(func.ST_Transform(func.ST_Centroid(grids.c.geom), epsg)).label('lat_proj')).all()
obj_results = []
for res in results:
obj_results.append(grid_obj(gid=res[0], centroid=res[1], lon=res[2], lat=res[3],
geom=res[4], lon_proj=res[5], lat_proj=res[6]))
# session.add_all(obj_results)
# session.commit()
return
except Exception as e:
print(e)
exit(-1)
def show_polygons(place_identifier):
place = get_place(place_identifier)
num = 0
for chunk in place.polygon_chunk(size=64):
num += 1
print(chunk)
print()
print(num)
return
num = '(-?[0-9.]+)'
re_box = re.compile(f'^BOX\({num} {num},{num} {num}\)$')
# select ST_Dump(geom::geometry) as poly from place where osm_id=1543125
stmt = (database.session.query(
func.ST_Dump(Place.geom.cast(Geometry())).label('x')).filter_by(
place_id=place.place_id).subquery())
q = database.session.query(
stmt.c.x.path[1],
func.ST_Area(stmt.c.x.geom.cast(Geography)) / (1000 * 1000),
func.Box2D(stmt.c.x.geom))
print(q)
for num, area, box2d in q:
# west, south, east, north
# BOX(135.8536855 20.2145811,136.3224209 20.6291059)
size = wikidata_chunk_size(area)
west, south, east, north = map(float, re_box.match(box2d).groups())
bbox = (south, north, west, east)
# print((num, area, size, box2d))
for chunk in chunk_n(bbox, size):
print(chunk)
def add_airspace(self, country_code, airspace_class, name, base, top, geom_str):
try:
geom = loads(geom_str)
except ReadingError:
print name + "(" + airspace_class + ") is not a polygon (maybe not enough points?)"
return False
# orient polygon clockwise
geom = polygon.orient(geom, sign=-1)
if not airspace_class:
print name + " has no airspace class"
return False
base = self.normalise_height(base, name)
top = self.normalise_height(top, name)
flightlevel_re = re.compile(r'^FL (\d+)$')
match = flightlevel_re.match(base)
if match and int(match.group(1)) >= 200:
print name + " has it's base above FL 200 and is therefore disregarded"
return False
airspace = Airspace()
airspace.country_code = country_code
airspace.airspace_class = airspace_class
airspace.name = name
airspace.base = base
airspace.top = top
# Check geometry type, disregard everything except POLYGON
if geom.geom_type != 'Polygon':
print name + " is not a polygon (it's a " + geom.geom_type + ")"
return False
wkb = from_shape(geom, srid=4326)
# Try to fix invalid (self-intersecting) geometries
valid_dump = (func.ST_Dump(func.ST_MakeValid(wkb))).geom
valid_query = db.session.query(func.ST_SetSRID(valid_dump, 4326)).order_by(func.ST_Area(valid_dump).desc()).first()
if not valid_query:
print 'Error importing ' + name
print 'Could not validate geometry'
return False
else:
wkb = valid_query[0]
geom_type = db.session.query(func.ST_GeometryType(wkb)).first()[0]
if geom_type != 'ST_Polygon':
print name + " got some errors makeing it valid..."
return False
tolerance = 0.0000001
simplify = lambda x: func.ST_SimplifyPreserveTopology(x, tolerance)
airspace.the_geom = case(
[
(func.ST_IsValid(wkb), wkb),
(func.ST_IsValid(simplify(wkb)), simplify(wkb)),
],
else_=None)
db.session.add(airspace)
return True