Postgres queries

From DreamsteepWiki

Jump to: navigation, search

POSTGRES/POSTGIS QUERIES

NOTES (http://postgis.refractions.net/docs/ch04.html#id2534259)


HEXAGONAL GRID 

CREATE TABLE hex_grid (gid serial not null primary key);
SELECT addgeometrycolumn('hex_grid','the_geom', -1, 'POLYGON', 2);

INSERT INTO hex_grid (the_geom)
SELECT st_translate(the_geom, x_series, y_series)
from generate_series(0 - 128, 10000 + 128, 128) as x_series,
generate_series(0 - 128, 10000 + 128, 256) as y_series,
(
   SELECT 'POLYGON((0 0,64 64,64 128,0 192,-64 128,-64 64,0 0))'::geometry as the_geom
   UNION
   SELECT translate('POLYGON((0 0,64 64,64 128,0 192,-64 128,-64 64,0  0))'::geometry, 64, 128)  as the_geom
) as two_hex

BUFFER FROM POLY GEOM 

SELECT st_asText ( ST_Buffer ( GeomFromText('POLYGON((1320886.77951984 11237288.9757078,1322087.1458855 11237238.8265299,1322081.90016109 11236673.6445894,1322057.38577442 11234567.8843653,1321110.10387634 11234580.7268593,1321119.84303009 11235174.7017212,1320264.92676092
11235175.4517197,1320275.03172759 11237322.6564148,1320886.77951984 11237288.9757078))') ,1000) );

UNION FROM FIG AND POLYGON 


SELECT st_astext(ST_UNION(the_geom) ) FROM taxlot_polygons WHERE st_dwithin( GeomFromText( 'POLYGON((1317250.7480469 11246610.694336,1322321.5917969 11245976.838867,1323272.375 11240166.49707,1318518.4589844 11240272.139648,1312708.1171875 11242807.561523,1317250.7480469 11246610.694336))',26915),taxlot_polygons.the_geom,0)

UNION (UNTESTED) 


select st_asText ( ST_union((the_geom)) ) from taxlot_polygons WHERE gid < 10 GROUP BY xcoord 




--SELECT ST_AsText (the_geom), FROM taxlot_polygons As f
--GROUP BY the_geom

SELECT   ST_Multi(ST_LineMerge(ST_Collect(the_geom)))  AS geom
FROM taxlot_polygons
GROUP BY xcoord ;

--SELECT * from taxlot_polygons where


ITERSECT ACTUAL POLYGONS (DEFINE SRID) 

SELECT * FROM taxlot_polygons where st_dwithin( 
GeomFromText('POLYGON((1317250.7480469 11246610.694336,1322321.5917969 11245976.838867,1323272.375 11240166.49707,1318518.4589844 11240272.139648,1312708.1171875 11242807.561523,1317250.7480469 11246610.694336))', 26915)
,taxlot_polygons.the_geom,0);

SET SRID

WRONG 

SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
((33595.443392445566 15067.465440617714,281187.7093687274
15067.465440617714,281187.7093687274
254932.5345593823,33595.443392445566
254932.5345593823,33595.443392445566 15067.465440617714))'))

RIGHT 

SELECT the_geom FROM reflijst WHERE ((the_geom && GeomFromText(
'POLYGON((33595.443392445566 15067.465440617714, 281187.7093687274
15067.465440617714, 281187.7093687274 254932.5345593823,33595.443392445566
254932.5345593823,33595.443392445566 15067.465440617714))', 103300)))

create table points ( pt geometry, name varchar );
 insert into points values ( 'POINT(0 0)', 'Origin' );
 insert into points values ( 'POINT(5 0)', 'X Axis' );
 insert into points values ( 'POINT(0 5)', 'Y Axis' );
 select name, AsText(pt), ST_Distance(pt, 'POINT(5 5)') from points;
 drop table points;


&&    This operator tells whether the bounding box of one geometry intersects the bounding box of another.

~=    This operators tests whether two geometries are geometrically identical. For example, if 'POLYGON((0 0,1 1,1 0,0 0))' is the same as 'POLYGON((0 0,1 1,1 0,0 0))' (it is).

=   This operator is a little more naive, it only tests whether the bounding boxes of two geometries are the same


select by buffer 

  SELECT DISTINCT ON (tp.gid ) tp.gid ,tp.the_geom
    FROM taxlot_polygons AS tp
    LEFT JOIN taxlot_polygons ON st_dwithin( tp.the_geom,taxlot_polygons.the_geom,1000) WHERE taxlot_polygons.gid =100;
 

text query with typecasting (varchar to text)

SELECT gid FROM taxlot_polygons WHERE taxlot_polygons."ACCTNODASH"=CAST('000915001000'AS text)



SELECT ST_Buffer( ST_GeomFromEWKT('POLYGON((1331188.69887659 11236661.4100338,
1334039.56751867 11236200.553344,1333752.13928775 11234302.7313523,1331931.553362 
11234617.4101848,1331680.10406175 11233141.8521123,1331620.55562442 
11232792.4085855,1331366.92128917 11232831.5653313,1331109.94312859 
11232871.2384803,1330749.61051592 11232926.8676342,1330115.24564292 
11233024.8028058,1329792.40278467 11233075.8259977,1329859.48303109 
11233516.972097,1330092.02784159 11234925.3203302,1330356.26878292 
11236493.438585,1330403.20668117 11236810.6046173,1331188.69887659 
11236661.4100338 ))') ,10,10)


BUFFER 

SELECT buffer(the_geom,.1) FROM taxlot_polygons WHERE gid = 11


BUFFER 

CREATE TABLE rrr ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'rrr','geom',-1,'POLYGON',2);

INSERT INTO rrr (ID, NAME, GEOM) 
VALUES (
  1, 
  'first geometry', 
  ST_Buffer(ST_MakePoint(4,10),10,10)
);




SELECT 
  taxlot_polygons."ACCOUNTNO"
FROM 
  taxlot_polygons

WHERE gid = 2112  ;

GET SRID 

SELECT ST_SRID(the_geom) FROM post_wgs


get centroid of geometry based on feature id 


SELECT ST_x (centroid(the_geom))  FROM post_wgs WHERE id=0 ;
SELECT ST_y (centroid(the_geom))  FROM post_wgs WHERE id=0 ;


http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.spatial.doc/spat140.htm 

SELECT ST_AsText(geom) FROM gtest;



SELECT ST_AsText(geom) FROM gtest WHERE id = 1; 



SELECT ST_AsText(the_geom) from post_wgs WHERE id=3


RETRIEVE SOME VALUES FROM A LINKED TABLE 

SELECT id FROM post_wgs WHERE name='dog';

SELECT name FROM post_wgs WHERE id=3;

SELECT id,name from post_wgs WHERE id=3


TYPE CASTING 

SELECT substr(CAST (1234 AS text), 3);



VARIOUS BBOX METHODS 

SELECT  ST_Box2D(the_geom) FROM post_wgs



SELECT ST_Extent3D(geom ) FROM gtest;

bbox from postgis (untested) 

SELECT Envelope(the_geom) post_wgs




SELECT * FROM characters WHERE character ='Daphne';




SELECT ST_AsGML(the_geom,5) as gml, ST_AsKML(the_geom,5) As kml,
ST_AsGeoJSON(the_geom,5) As geojson,
ST_AsSVG(the_geom,0,5) As svg_no_moves, ST_AsSVG(the_geom,1,5) As
svg_relmoves, ST_GeoHash(the_geom) As geohash
FROM (SELECT
ST_GeomFromText('LINESTRING(-120.2911 41.3664,-120.2915 41.3656)',
4326) As the_geom ) As foo;


SELECT postgis_full_version();
SELECT version();


make a point in epsg code 4326 

SELECT ST_SetSRID(ST_Point(-77.036548, 38.895108),4326);



SELECT
ST_AsEWKT('0101000020E6100000FD2E6CCD564253C0A93121E692724340');


select by geometry 


SELECT road_id, road_name 
  FROM roads 
  WHERE roads_geom ~= ST_GeomFromText('LINESTRING(191232 243118,191108 243242)',-1);


select by intersect 

SELECT road_id, road_name 
FROM roads 
WHERE roads_geom && ST_GeomFromText('POLYGON((...))',-1);


GET BBOX 

SELECT ST_XMin(the_geom) FROM post_wgs;
SELECT ST_XMax(the_geom) FROM post_wgs;
SELECT ST_YMin(the_geom) FROM post_wgs;
SELECT ST_YMax(the_geom) FROM post_wgs;



# ST_Box2D, ST_Box3D and Casting a Box to a geometry

SELECT ex_name, ST_Box2D(bbox3d) As bbox2d , bbox3d,
ST_AsEWKT(CAST(bbox3d As geometry)) AS bboxwktgeom
FROM (
VALUES
('A 2d line', ST_Box3D(ST_GeomFromText('LINESTRING(1 2, 3 4)') )),
('A vertical line', ST_Box3D(ST_GeomFromText('LINESTRING(1 2, 1 4)'))),
('A 3d line', ST_Box3D(ST_GeomFromEWKT('LINESTRING(1 2 -1, 3 4 1)'))),
('A point',ST_Box3D(ST_GeomFromText( 'POINT(1 2)'))) ,
('A triangle', ST_Box3D(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')) )
)
AS foo(ex_name, bbox3d);


BBOX SELECT 

SELECT ST_AsText(roads_geom) AS geom 
FROM roads 
WHERE 
  roads_geom && SetSRID('BOX3D(191232 243117,191232 243119)'::box3d,-1);

















SELECT ST_ST_AsText( ST_GeomFromText('POINT(-71.064544 42.28787)') );
SELECT ST_AsGML    ( ST_GeomFromText('POINT(-71.064544 42.28787)') );








shp2pgsql shaperoads myschema.roadstable | psql -d roadsdb
























Create some stinkin geometry




CREATE TABLE gtest ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'gtest','geom',-1,'LINESTRING',2);

INSERT INTO gtest (ID, NAME, GEOM) 
VALUES (
  1, 
  'First Geometry', 
  ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)', -1)
);








CREATE TABLE gtestt ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'gtestt','geom',-1,'POLYGON',2);

INSERT INTO gtestt (ID, NAME, GEOM) 
VALUES (
  1, 
  'First Geometry', 
  ST_GeomFromText('POLYGON((0 1,1 -1,-1 -1,0 1))')
);








CREATE TABLE pointz ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'pointz','geom',-1,'POINT',2);

INSERT INTO pointz  (ID, NAME, GEOM) 
VALUES (
  1, 
  'points Geometry', 
  ST_GeomFromText('POINT (-1 1)')
);









CREATE TABLE gtestt ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'gtestt','geom',-1,'MULTIPOINT',2);

INSERT INTO gtesttt (ID, NAME, GEOM) 
VALUES (
  1, 
  'points Geometry', 
  ST_GeomFromText('MULTIPOINT (-1 1, 0 0, 2 3)')
);








from http://postgis.refractions.net/documentation/manual-svn/ST_GeomFromGML.html



Instead of grouping by oid and ogc_fid, try polygonizing all of the
geometries into a single geometry collection, then use dump() to
extract the individual polygons.  This works for me with your dataset:

SELECT astext(geom)
FROM dump((
  SELECT polygonize(the_geom)
  FROM countries_dcw_test
  WHERE cntry_code = 'QT'
)); 








Retrieved from "http://wiki.dreamsteep.com/Postgres_queries"

						
						

		

	

		

		

	
	

		
Personal tools