PostgreSQL | 语句Mark

计算距离：

select name,st_astext(geom) as geomtext,?st_distance(Geography(ST_PointFromText(‘POINT(121.43543 31.2399)‘,4326)),Geography(geom)) as distance?from cz_basedata_other_l?? where ST_Intersects(st_buffer(geography(ST_PointFromText(‘POINT(121.43543 31.2399)‘,3000),geom) and dlvydate> date ‘2010-01-01‘

?

交叉计算：

select st_astext(geom) as GeomText,"County",jdlon as Centerx,jdlat as Centery,( people::DECIMAL/area::DECIMAL*1000000.0 )::INT as Density from vtown

where?ST_Intersects(st_buffer( geography( ST_PointFromText(‘POINT(121.492498 31.229649)‘,4000),geom)

?

数组转换为geometry(对百度数组也适用)：

select? name from sqs.sqs_pipeline where?ST_Intersects(ST_GeomFromText(‘POLYGON((121.550391 31.048365,121.845897 31.063214,121.734364 30.859081,121.580286 30.940391,121.550391 31.048365))‘,4326),geom)

select? name from sqs.sqs_pipeline where ST_Within(geom,ST_PolygonFromText(‘POLYGON((121.550391 31.048365,4326)) and status=1

?

根据geom计算x、y：update sqs.sqs_poi set lng=st_x(geom),lat=st_y(geom)

?

获取点geometry的点x、y坐标：

select *,?st_x(geom),st_y(geom)?from sqs.sqs_store where status=1 and username=‘admin‘ and? ST_Within(geom,ST_GeomFromText(‘POLYGON((121.289092 31.38394,121.333648 31.275857,121.462429 31.333862,121.459267 31.38172,121.379928 31.405393,121.289092 31.38394))‘,4326))

?

点转换为geometry：

update sqs.sqs_pipeline?set?pipelineid=‘pp00000008‘,name=‘admin‘,?geom=ST_PointFromText(‘POINT(121.845897 31.063214?)‘,status=1;

update sqs.yichang set?geom=ST_PointFromText(‘POINT(‘||lng||‘ ‘||lat||‘)‘,4326);

update sqs.sqs_pipeline_wfztest set geomta500=ST_PolygonFromText(st_astext(st_buffer(geography(ST_PointFromText(‘POINT(‘||st_x(geom)||‘??‘||st_y(geom)||‘)‘,500 )),4326);

?

?

String?sql?=?"select?st_Area(Geography(st_intersection(a.geom,b.geom)))/st_Area(geography(a.geom)) as per100 "

??????????????? +?"from sqs.sqs_city a,"+tablename?+" b "

??????????????? +?"where st_Intersects(a.geom,b.geom) and b."+id+"=‘"+?storeid+?"‘ and b.username=‘"+username?+"‘ and b.status=1"?;

?

String?sql?=?"select ceil(totarea) as totarea,st_asgeojson(b.geom) as geometry,ceil(st_distance(Geography(ST_PointFromText(‘POINT("?+?x?+?" "?+?y?+")‘,Geography(b.geom))) as distance from sqs.sqs_newhouse b "

??????????????? +?"where ST_Intersects(ST_PolygonFromText(‘POLYGON(("+p+"))‘,b.geom) and "

??????????????? +"dlvydate > ‘2010-1-1‘ "

??????????????? +??"order by tothh desc "?+??"limit 10";???

?

建立一张新表并从另一张已存在的表复制表结构及数据：

create table town_20150525? as??select * from vtown_20150526?

?

添加字段（自增长序列）：

alter table cz_users add column gender character varying(50)?unique

alter?table cz_udatarange add column id??serial

unique 唯一值?????timestamp without time zone DEFAULT now(),时间????yyyy-MM-dd hh:mm:ss

?

单独添加列??空间面：select?addgeometrycolumn(‘sqs‘,‘sqs_province‘,‘geom‘,4326,‘MULTIPOLYGON‘,2,TRUE)

?

前几条数据：LIMIT?num??OFFSET?startNum??可以实现指定条数开始取几条

?

替换replace：update cz_basedata_test2 set?cnty=replace(cnty,city,‘‘)//不可为null，否则整列将会变为null

?

sqs.sqs_pineline?等表中的pipelineid、userid赋值方法：

update sqs.sqs_pipeline set userid=‘24c19d21-f519-4a49-869d-6455787537a3‘,status=1,pipelineid=‘pp‘||to_char(id,‘FM00000000‘),name=‘pp‘||to_char(id,‘FM00000000‘)?where userid is null

update sqs.sqs_competitor??set?storeid=‘cc‘||to_char(id,userid=‘e27accfb-e5ff-409c-830f-4a67387237ff‘??where userid is null

update sqs.sqs_store set userid=‘55bf68a3-adf2-49d6-89f8-aa05f59cbe01‘,username=‘xxx‘,storeid=to_char(id,‘FM00000000‘)?where userid is null

?

sql语句中+号:update?sqs.sqs_streets?set?geom=ST_PointFromText(‘POINT(‘||lng||‘?‘||lat||‘)‘,4326)