Tag Info

Hot answers tagged

3

For example, if you have a row in your table which you want to select as the center of the radius, and the numerical column in which you're storing the victims is called deaths: WITH subquery AS (SELECT the_geom FROM tablename WHERE name = 'this is my center') SELECT sum(tablename.deaths) FROM tablename, subquery WHERE ST_Intersects(tablename.the_geom, ...


2

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this: SELECT A.* FROM osm_addr2 AS addr, osm_addr2 AS POI WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry); There's a relevant example in the manual (last two SQL examples), where a subquery is ...


2

@BradHards thank you for your inquiry, you led me to investigate how my ORM was executing the query. Instead of applying ST_AsGeoJSON() to the column, I was inadvertently applying the function to each data record, thereby adding an extra query for each record returned. After I manually executed the query, everything worked much faster! BEFORE: SELECT ...


2

I can't leave a comment due to low reputation, so will extend @pistachionut's answer as a separate one. In my case the contents of /usr/local/pgsql/data are owned by user _postgres. Thus, to recreate missing directories it is necessary to run the commands as that user: sudo -u _postgres mkdir -p /usr/local/pgsql/data/{pg_tblspc,pg_twophase,pg_stat_tmp}/ ...


2

As has been suggested earlier and in the ST_Split documentation, you must first snap your line to the points and then call ST_Split. If you are like me, an example is worth more than words: DROP TABLE IF EXISTS split; CREATE TABLE split AS( SELECT (ST_Dump(ST_Split(ST_Snap(a.the_geom, b.the_geom, 0.00001),b.the_geom))).geom FROM line_table a JOIN ...


2

Sure, using ST_Contains SELECT lines.* FROM lines, polygons WHERE ST_Contains(polygons.geom, lines.geom)


2

Try something like this, sorry had to replace your variables with real values so I could test, but should be easy enough for you to replace back with what you had. Also note I replace your ST_MakeBox2D/ST_SetSRID with ST_MakeEnvelope which is shorter and generally more accurate. WITH c AS (SELECT -88.505 As topLon, 41.8046 As topLat, -88.405 As botLon, ...


2

ST_StartPoint is the correct function to find single nodes at the start of a Linestring, however it does not work with MultiLinestring, so you will need to use ST_Dump to get the constituent Linestrings. If I have understood you question correctly, you then want all start points which are not also end points for more than one line, ie, points where two ...


2

Look at creating a role (and possibly a user in that role) that only has SELECT privileges on a table, schema or group of tables. See the Postgres docs on creating roles. Once you have created a role, look at granting that role particular permissions. Available permissions are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and so you want ...


1

From what you described, you don't need any extra common keys, as the geometries will take that role. So just create your query and join the tables spatially. Eg. something like this with corrected names and fields you want to select: SELECT congdist.name, congdist.representative, territories.info FROM congdist, territories WHERE ...


1

Apperantly, from your question I think you need to have some basic concept on database management(esp spatial database) and GIS softwares(QGIS) too.Both QGIS and PostgreSQL have comprehensive documentation you really need to consult with. To add @ John Barça, You can not connect postgresql to qgis rather reverse is done to solve the problem. If you need to ...


1

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)' LANGUAGE 'sql' IMMUTABLE; And so the query gets rewritten to use ...


1

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan. SELECT * FROM osm_addr2 AS addr, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...


1

I had a similar problem an I solved it with the following statement: SELECT city.name, postcode.tags->'postal_code' FROM planet_osm_polygon postcode JOIN planet_osm_polygon city ON ST_INTERSECTS(city.geometry, postcode.geometry) AND NOT ST_Touches(city.geometry, postcode.geometry) WHERE a.name = 'cityname' AND b.boundary = 'postal_code'; ...


1

It looks fine and the distance is always measured in the units the SRID used defines. For 4326 that would be in angular units. Unless you use geographies like you found out, that is. Read more in the official docs here. Why 100 km is needed for Chicago is up to you and your data. It doesn't sound that much for a huge city and in fact if I check Wikipedia, ...


1

You'll need to translate the UTM zone to an EPSG code, use ST_FromText to create a geometry object in that projection, and then use ST_Transform to transform your geometry object into whatever SRID you are using for the entire table. I haven't tried it, but this link gives a description of how to programatically convert from latitude/longitude to UTM zone; ...


1

I think you should be able to use this function (from here): -- Function: utmzone(geometry) -- DROP FUNCTION utmzone(geometry); -- Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom)) ) FROM sometable; CREATE OR REPLACE FUNCTION utmzone(geometry) RETURNS integer AS $BODY$ DECLARE geomgeog geometry; zone int; pref ...


1

Use spatial join between your points and your lines, with the JOIN_ONE_TO_ONE option. You can get attributes from both points using the "first" and "last" merge rules. If the order is important, generate each end point type (START /END) separately using feature vertices to points then join.


1

Is there a reason you have mycoords in a separate table from mypoints? If no reason I would combine them. For example do you do that because if a point changes you expect all geometries that had that point to change (so yo have a 1 to many?). If it's always a 1 to 1 I would collapse the two into one table to make your life easier. The main issue I see ...



Only top voted, non community-wiki answers of a minimum length are eligible