'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
------------------------------------------------------------------------
find all the city parks, which within or touch the rectangular bound(polygon(lng,lat)..)
above sql will output geojson, but not yet, assume'result" is whatever postgresql output, the real valid geojson is in result.rows[0].json_build_object
for example you use node.js the response send should be: res.send(result.rows[0].json_build_object);
more explain about the sql:
::json means cast to json type ::jsonb means cast to jsonb type( json binary type)
jsonb_set() because the row_to_json() will convert all column include geom to properties list, which you do not want geom column, you want to remove it, but so far not remove method provided by postgresql json operation method, so I use jsonb_set to reset geom to '0', here you can't use null, otherwise, all properties list will be null.
city_parks is the table name in postgresql, geom is the geometry column name.
geojson's SRID is always 4326
city_parks 's SRID is 2230
ST_ForceRHR() is used because geojson polygon follow right hand rule, this will fore polgyon vertice follow right hand rule
when you use st_intersects(a, b), a and b both must be geometry or both must be geography type.
st_interesects(geometry, geography) // invalid
city parks geom is geometry type with SRID=2230,
Bounding box polygon((lng,lat),(lng,lat)...) is geography type with SRID=4326,
st_transform() was used to either convert from SRID(2230) to SRID(4326) or like wise
row_to_json() will convert all column in this row to json
select row_to_json(city_parks) from city_parks;
{"id":6013,"column_name_1":"value1","column_name_2":"value2",... etc}
-------------------------------------------------------------------------------------------------------
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks
***************************
st_intersects( st_transform(geom, 4326):geography, bounding box(lng,lat)
********************************
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326)::geography, ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
********************
or, you can st_intersect(geom, st_transform( bounding box(lng,lat):geometry , 2230))
*************************************
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
both work, st_intersects(A, B)
A, B both be geometry, or Both be geography.
A,B SRID must be same, if different, must use st_transform() to make both SRID same.
-----------------------------------------------------------------------
::geography will cast geometry to geography
::geometry will cast geography to geometry
geojson polygon right hand rule does not matter, you can clock wise or anti-clock wise.
geom column in city_park table are geometry with special SRID, if you want to use
ST_intersects, you must st_transform(geom, 4326)
Because the lat long bounding box you get SRID=4236,
so you have to transform geom to 4326 before run st_intersects.
==================================================
SELECT Find_SRID('public','city_parks','geom')
select st_asgeojson(st_transform(st_setsrid(geom,2230),4326)) from city_parks;
select st_asgeojson(st_transform(geom,4326)) from city_parks;
+++++++++++++++++++++++++++++++++++
samples:
=================================
{
"type": "Polygon",
"coordinates": [
[
[-117.963690, 33.634180],[-117.854780, 33.634180],[-117.854780, 33.702970],[-117.963690, 33.702970],[-117.963690, 33.634180]
]
]
}
small box of city
-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995
====================================
select st_asgeojson(st_transform(st_setsrid(geom,2230),4326)) from city_parks;
select st_asgeojson(st_transform(geom,4326)) from city_parks;
SELECT Find_SRID('public','city_parks','geom')
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326)::geography, ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Within(st_transform(city_parks.geom,4326)::geography, ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
select count(*) as total from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
2 sec
select count(*) as total from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
1 sec
.......................................
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
ST_ForceRHR()
select name_alf, st_asgeojson(st_transform(ST_ForceRHR(geom),4326)) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
select name_alf, st_asgeojson(ST_ForceRHR(st_transform(geom,4326))) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
[27 sec]
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))')::geometry,2230));
[3 sec]
select count(*) as total from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
224 ms
select count(*) as total from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))')::geometry,2230));
124 ms
..........................................
geojson polygon must force right hand rule
ST_ForceRHR()
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks
select name_alf, st_asgeojson(ST_ForceRHR(st_transform(geom,4326))) as latlng from city_parks
select name_alf, st_asgeojson(geom) as latlng from city_parks
select name_alf, st_asgeojson(ST_ForceRHR(geom)) as latlng from city_parks
---------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', json_build_object(
'name_alf', name_alf
)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
--------------------------------------------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', row_to_json(city_parks)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
----------------------------------------------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
======================
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
SELECT jsonb_set('{"bar":"baz","balance":1,"active":false}','{bar}','6',false);
SELECT jsonb_set('{"f1":1,"f2":null}', '{f1}','6', false)
=====================================================
select row_to_json(city_parks) from city_parks
SELECT UpdateGeometrySRID('city_parks','geom',2230);
+++++++++++++++++++++++++++++++++++
samples:
=================================
{
"type": "Polygon",
"coordinates": [
[
[-117.963690, 33.634180],[-117.854780, 33.634180],[-117.854780, 33.702970],[-117.963690, 33.702970],[-117.963690, 33.634180]
]
]
}
small box of city
-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995
====================================
select st_asgeojson(st_transform(st_setsrid(geom,2230),4326)) from city_parks;
select st_asgeojson(st_transform(geom,4326)) from city_parks;
SELECT Find_SRID('public','city_parks','geom')
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326)::geography, ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(st_transform(city_parks.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Within(st_transform(city_parks.geom,4326)::geography, ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
select count(*) as total from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))'));
2 sec
select count(*) as total from city_parcels WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
1 sec
.......................................
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
ST_ForceRHR()
select name_alf, st_asgeojson(st_transform(ST_ForceRHR(geom),4326)) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
select name_alf, st_asgeojson(ST_ForceRHR(st_transform(geom,4326))) as latlng from city_parks WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
[27 sec]
select address, st_asgeojson(st_transform(geom,4326)) as latlng from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))')::geometry,2230));
[3 sec]
select count(*) as total from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
224 ms
select count(*) as total from city_parcels WHERE ST_Intersects(city_parcels.geom,st_transform(ST_GeographyFromText('POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))')::geometry,2230));
124 ms
..........................................
geojson polygon must force right hand rule
ST_ForceRHR()
select name_alf, st_asgeojson(st_transform(geom,4326)) as latlng from city_parks
select name_alf, st_asgeojson(ST_ForceRHR(st_transform(geom,4326))) as latlng from city_parks
select name_alf, st_asgeojson(geom) as latlng from city_parks
select name_alf, st_asgeojson(ST_ForceRHR(geom)) as latlng from city_parks
---------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', json_build_object(
'name_alf', name_alf
)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
--------------------------------------------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', row_to_json(city_parks)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
----------------------------------------------------------------------
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(city_parks.geom,st_transform(ST_GeographyFromText('POLYGON((-117.963690 33.634180,-117.854780 33.634180,-117.854780 33.702970,-117.963690 33.702970,-117.963690 33.634180))')::geometry,2230));
======================
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', gid,
'geometry', ST_AsGeoJSON(ST_ForceRHR(st_transform(geom,4326)))::json,
'properties', jsonb_set(row_to_json(city_parks)::jsonb,'{geom}','0',false)
)
)
)
FROM city_parks
WHERE ST_Intersects(st_transform(city_parcels.geom,4326), ST_GeographyFromText('SRID=4326;POLYGON((-117.935228 33.656995,-117.935228 33.640705,-117.913642 33.640205,-117.914071 33.657138,-117.935228 33.656995))'));
SELECT jsonb_set('{"bar":"baz","balance":1,"active":false}','{bar}','6',false);
SELECT jsonb_set('{"f1":1,"f2":null}', '{f1}','6', false)
=====================================================
select row_to_json(city_parks) from city_parks
SELECT UpdateGeometrySRID('city_parks','geom',2230);
No comments:
Post a Comment