osm导入到postgresql

osm2pgsql github 地址：https://github.com/openstreetmap/osm2pgsql.git

osm2pgsql -H 10.10.11.191 -P 5432 -U postgres -W -d maps -O flex -s -S ./osm2pgsql/flex-config/attributes.lua --extra-attributes china-latest.osm.pbf

--extra-attributes 参数用于从osm中获取创建时间等信息

osm中资源类型说明：https://wiki.openstreetmap.org/wiki/Map_features

osm2postgrsql ： Home - osm2pgsql

openstreetmap：OpenStreetMap

查询指定经纬度半径内资源数量：

CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
		i                 geometry;
		transform_i       geometry;
		multiArr          geometry[]; 
	
BEGIN
	IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
	end if;
 	CASE ST_GeometryType(geom)
    	when 'ST_LineString' then 
			return geoc_bd09towgs84_line(geom);
		when 'ST_MultiLineString' then 
			return geoc_bd09towgs84_multiline(geom);
		when 'ST_Point' then 
			return geoc_bd09towgs84_point(geom);
		when 'ST_MultiPoint' then 
			return geoc_bd09towgs84_multipoint(geom);
		when 'ST_Polygon' then 
			return geoc_bd09towgs84_polygon(geom);
		when 'ST_MultiPolygon' then
			return geoc_bd09towgs84_multipolygon(geom);
		ELSE
     		RETURN null;
	END CASE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

with cte as (
SELECT ST_Transform(ST_SetSRID(geoc_bd09towgs84(ST_GeomFromText('POINT(120.286093 31.465852)', 4326)), 4326), 3857) as point 
)
SELECT lines.*
FROM lines,cte
WHERE ST_DWithin(geom ,cte.point, 1000);

with cte as (
SELECT ST_Transform(ST_SetSRID(geoc_bd09towgs84(ST_GeomFromText('POINT(120.286093 31.465852)', 4326)), 4326), 3857) as point 
)
SELECT lines.* from lines,cte 
        WHERE geom && ST_Expand(cte.point, 1000) ;