Tag Info

New answers tagged

0

you could also load your postgis-Layer into qgis and save it within qgis itself as a shapefile (right-mouse-click on the layer in the TOC --> save as. Then you can choose the encoding: ( If you choose something else than "system" as encoding you will probably have to choose the encoding while loading the shapefile into qgis in order to get all your special ...


0

Assuming you can reproduce the error, you can get the exact error code yourself pretty easily: DO $$ BEGIN PERFORM ST_Contains('problem 1 geom here'::GEOMETRY, 'problem geom 2 here'::GEOMETRY); EXCEPTION WHEN others THEN RAISE NOTICE 'Error code: %', SQLSTATE; RAISE NOTICE 'Error message: %', SQLERRM; END; $$ (Put your actual ...


0

I was able to force PostgreSQL to use the data folder F:\db\ by creating the following batch file (and manually turning PostgreSQL on/off): start "" /min "C:\<PostregreSQL>\bin\pg_ctl.exe" start -D "F:\db" -w I found this solution here: http://www.garretwilson.com/blog/2014/03/09/postgresql-management-windows.xhtml


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

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 ...


2

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


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 ...


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 ...


0

Judging by the cost and quantity of rows estimated to be returned, the spatial index wasn't necessary. At least the optimizer didn't think it was and chose a seq scan instead. Is it running slow? How may rows do you expect back? How many rows are in the table altogether? Sorry if I overlooked that in your question but sometimes a full scan is faster. ...


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'; ...


0

I want to expand on the answers above from a beginner's perspective. In this scenario, you have a series of points and you watch to use them as a "blade" to cut a lines into segments. This whole example assumes that you first snapped your points to the line and that the points have the unique ID attribute from their snapped line. I use 'column_id" to ...


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, ...


0

I've found that (re)sorting the field (by clicking the field name header at the top) will force the attribute table to refresh with the new values. This also works if you're editing directly in QGIS. You can get the map data to update either by zooming or just a quick nudge/pan with the mouse, or by hitting the refresh button.


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

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, ...


0

In your postgres-directory(e.g. C:\Program Files\PostgreSQL\9.3\ ) you will find a subdirectory called "share". There you can find the sample-file "pg_service.conf.sample". Create a Service-Definition here like this: [testconnection] host=localhost dbname=mytestdb user=postgres port=5432 password=postgres Save this file as ".pg_service.conf" (the dot ...


0

You could use the information here to generate a SRID - basically add the zone number to 32600 (in the north) and 32700 in the south. This works if you can use WGS84 as the geographic coordinate reference system. The utm_zone_letter identifies the latitude band. If the data is always in the northern hemisphere, then you could ignore it and use 32600 + ...


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 ...


0

I've had the same error. In my case the solution is also the path name. The path name must not have any special characters like ä,ö or ü.


0

Working on the assumption that the polygons are only ever going to be at the end of lines, your query should return each polygon from your diagram, but not in the format you have indicated. This makes me think that you have a small gap between your polygon and the end of the line. I think the tolerance is 0.00001 for ST_Intersects. Rather than using ...


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.


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

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}/ ...


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 ...


0

It depends what you want to do: I don't think Postgresapp comes packaged with address_standardizer or pgRouting, so no you can't install those via CREATE EXTENSION and really can't install them at all unless you compile them. pgRouting is for network routing, so if you have no need for building trip navigation, you probably don't need it. Most people ...


0

You can use this for PostgreSQL. 600 is 10 minutes in seconds. The idea is to convert timestamp to epoch, divide by interval desired in minutes then round to get the desired interval SELECT COUNT(*) cnt, to_timestamp(floor((extract('epoch' from timestamp_column) / 600 )) * 600) AT TIME ZONE 'UTC' as interval_alias FROM TABLE_NAME GROUP BY interval_alias ...


0

I cannot answer if you need those extensions or not. To add it to a database, you need to install the package of the extension and execute it on psql: \c your_database_name; create extension address_normalizer;


0

Thank you @dkastl you were right. The function I was looking for was pgr_dijkstra. I also needed to declare one of my columns as an integer for the function to work


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 ...



Top 50 recent answers are included