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