postgis的外部数据包装器
geofdw的Python项目详细描述
geofdw是与PostGIS相关的集合 foreign data wrappers用于 PostgreSQL用python编写,使用 multicorn扩展名。通过使用FDW,您可以 通过postgres表访问空间数据,无需导入 数据优先,可用于动态或非表格数据 通过Web服务提供。
当前实现的转发数据包装器是:
- fgeocode:正向地理编码
- rgeocode:反向地理编码
- geojson:在线geojson
- 随机点:边界框中的随机点
geofdw使用plpygis来 操作postgis几何图形。
示例
fgeocode
为所有转发地理编码表创建一个服务器:
# CREATE SERVER fwd_geocode FOREIGN DATA WRAPPER multicorn OPTIONS ( wrapper 'geofdw.FGeocode' );
创建两个表,一个使用googlev3地理编码器,另一个使用 提名:
# CREATE FOREIGN TABLE fgc_google ( query TEXT, rank INTEGER, address TEXT, geom GEOMETRY ) SERVER fwd_geocode; # CREATE FOREIGN TABLE fgc_nominatim ( query TEXT, rank INTEGER, address TEXT, geom GEOMETRY ) SERVER fwd_geocode OPTIONS ( service 'nominatim');
从地理编码器中选择与查询字符串匹配的结果:
SELECT address, ST_AsText(geom) AS geom FROM fgc_google WHERE query = 'canada house' LIMIT 5; address | geom ----------------------------------------------------------------------------------------+------------------------------------ Canada House, London, UK | POINT Z (-0.1291 51.5077 0) Canada House, Temple Road, Blackrock, Co. Dublin, Ireland | POINT Z (-6.1756563 53.2994401 0) Canada House, Saint Stephen's Green, Dublin 2, Ireland | POINT Z (-6.2576992 53.335963 0) Canada House, 29 Hampton Road, Twickenham, Greater London TW2 5QE, UK | POINT Z (-0.3443802 51.441739 0) Canada House, 272 Field End Road, Ruislip, Greater London HA4 9NA, UK | POINT Z (-0.3973435 51.5752994 0)
执行相同的查询,但使用提名地理编码器:
# SELECT address, ST_AsText(geom) AS geom FROM fgc_nominatim WHERE query = 'canada house' LIMIT 5; address | geom -----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------- High Commission of Canada, 5, Trafalgar Square, Covent Garden, City of Westminster, London, Greater London, England, SW1Y 5BJ, United Kingdom | POINT Z (-0.129104703393283 51.50782475 0) Canada House, West 54th Street, Diamond District, Manhattan, New York, NYC, New York, 10019, United States of America | POINT Z (-73.9758789212845 40.7609797 0) Canada House, Kololo Road, Governments Cantonment, Juba, Central Equatoria, South Sudan | POINT Z (31.5901769 4.8615639 0) Canada House, Justine Close, Nalumunye, Kazinga, Wakiso, Central 2, Central Region, Uganda | POINT Z (32.4868336484328 0.25676655 0) בית קנדה, 1, שבי ציון, רובע א', אשדוד, מחוז הדרום, 77452, מדינת ישראל | POINT Z (34.64463585 31.8086878 0)
通过将查询限制在某个边界来影响地理编码器 框(在这种情况下,如果没有提示,google将只返回 美国):
# SELECT address, ST_AsText(geom) AS geom FROM fgc_google WHERE query = 'Water St' AND geom && ST_GeomFromEWKT('SRID=4326;POLYGON((50 2, 55 2, 55 -2, 50 -2, 50 2))'); address | geom ------------------------------------------------------------------------+----------------------------------- Water Street, Lavenham, Sudbury, Suffolk CO10 9RW, UK | POINT Z (0.7982752 52.1071416 0) Water Street, Stamford, Lincolnshire PE9 2NJ, UK | POINT Z (-0.4752917 52.649958 0) Water Street, Cambridge, Cambridgeshire CB4 1PA, UK | POINT Z (0.1474321 52.2184911 0) Water Street, Hampstead Norreys, Thatcham, West Berkshire RG18 0RU, UK | POINT Z (-1.2408465 51.4854726 0) Water Street, Burntwood, Staffordshire WS7 1AW, UK | POINT Z (-1.9355616 52.6839693 0) Water Street, Kettering, Northamptonshire NN16, UK | POINT Z (-0.7173979 52.4008413 0) Water Street, Birmingham, West Midlands B3 1HP, UK | POINT Z (-1.9028035 52.4854385 0) Water Street, London WC2R 3LA, UK | POINT Z (-0.1136366 51.5118691 0)