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);