postgresql语句

postgresql语句

1.postgresql获取geom数据

1.1 获取geom几何字段（数组形式）

SELECT gid as id, 
		name,
		st_astext(geom) 
		as location_shape
FROM public.gis_osm_roads_free_1 

1.2 获取geom几何字段（x,y形式）

 SELECT 
		place_id as id, 
		place_name as name, 
		concat(
		st_y(ST_Transform(geom, 4326)) , 
		',', 
		st_x(ST_Transform(geom, 4326))
		) as location
	FROM 
	public.place_point

2.获取某点100米内函数sql（速度慢）

SELECT *
FROM public.gis_osm_roads_free_1 t
WHERE ST_DWithin(ST_GeographyFromText('SRID=4326;POINT(120.6758186 31.3083266)'),t.geom::geography,100)

3.将lat，lng两个字段合并为geom几何字段

方式一

create table shanghaitest as select *,
	GeomFromText(CONCAT('POINT(',lng,' ',lat,')')) geom 
from shanghaitest01;

方式二（推荐）

UPDATE beijingsupermarket SET geom = ST_POINT(lng, lat);