INSERT INTO nyc_poi.pts (name, geom) VALUES ('Times Square', ST_SetSRID(ST_MakePoint(-73.985727,40.757685),4269));
OR
INSERT INTO nyc_poi.pts (name, geom) VALUES ('Times Square', ST_GeomFromText('POINT(-73.985727 40.757685)',4269));
SELECT name, ST_NPoints(geom) FROM states INNER JOIN census2010 ON states.name = census2010.state WHERE census2010.total > 10000000;
SELECT name, ST_Transform(geom,32015) FROM nyc_poi.pts;
OR for human-readable geom:
SELECT name, ST_AsText(ST_Transform(geom,32015)) FROM nyc_poi.pts;
SELECT name, ST_AsEWKT(geom) FROM usa.cities;
SELECT name, ST_AsText(ST_Centroid(geom)) FROM states INNER JOIN census2010 ON states.name = census2010.state WHERE male > female;
SELECT states.name FROM states CROSS JOIN cities WHERE ST_Contains(states.geom, cities.geom) AND cities.name = 'Springfield';
OR
SELECT states.name FROM states CROSS JOIN cities WHERE ST_Covers(states.geom, cities.geom) AND cities.name = 'Springfield';
SELECT cities.name, cities.stateabb FROM states CROSS JOIN cities WHERE ST_Within(cities.geom, states.geom) AND states.sub_region='Soda' ORDER BY cities.stateabb, cities.name;
OR
SELECT cities.name, cities.stateabb FROM states CROSS JOIN cities WHERE ST_Intersects(cities.geom, states.geom) AND states.sub_region='Soda' ORDER BY cities.stateabb, cities.name;
SELECT lines.name FROM lines CROSS JOIN pts WHERE pts.name = 'Madison Square Garden' AND ST_DWithin(ST_Transform(lines.geom,2260),ST_Transform(pts.geom,2260),5280*1);
SELECT cityA.name, cityB.name, ST_DistanceSphere(cityA.geom, cityB.geom) / 1000 AS dist_km FROM cities AS cityA CROSS JOIN cities AS cityB WHERE (cityA.capital = 1 AND cityB.capital = 1) AND (cityA.name != cityB.name);
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%Pennsylvania North%';
The correct spatial reference has an SRID of 2271.
SELECT DISTINCT states.name FROM states CROSS JOIN cities WHERE ST_Contains(states.geom, cities.geom) AND cities.popclass >= 4;
If you completed this exercise on your own, you probably had Texas appear in your results 3 times. Remember that a cross join creates a result set that combines all the rows from table A with all the rows from table B. The WHERE clause narrows down the cities in the output to those 9 having a popclass of 4 or 5, but 3 of those 9 cities are in Texas. That explains why the query returns Texas 3 times. The answer in a case like this is to insert the DISTINCT keyword after SELECT. This ensures that none of the output rows will be duplicated.