New answers tagged

1

Not sure why you are getting that error, here is another option to add the column using psycopg2: import psycopg2 conn = psycopg2.connect("dbname = 'dbName' port = '5432' user = 'postgres' host = 'serverName' password = '********'") cur = conn.cursor() sqlQuery = "ALTER TABLE tableName ADD COLUMN newColumnName numeric;" cur.execute(sqlQuery) conn.commit() ...


1

I had to face the same errors when trying to import PostGIS geometries into R. I could manage to iterate readWKT() through the polygons with the help of the apply-function-family: All credits to: https://spacetimecereal.com/2015/01/13/loading-postgis-geometries-into-r-without-rgdal-an-approach-without-loops/ Connect to PostgreSQL DB: drv <- dbDriver("...


1

You're missing a GROUP BY to specify aggregation of the rows. WITH clusters AS( SELECT row_number() OVER () AS cluster_id, unnest(ST_ClusterWithin(table.geom, 250)) AS gc FROM table) , unclustered AS( SELECT cluster_id, unnest(gc) AS geom FROM clusters) SELECT cluster_id as gid, string_agg(table.amenity, ';' ORDER BY table....


0

ST_Split will split a line by a multi polygon and return a geometry collection by which you can retreive the lat,long of the start and endpoints. This statement: uses some similiar test geometry of 1 multiline and 3 polygon circles: combines the 3 circles into a single geometry with Union All and ST_Union gets the intersected geometry between the line and ...


2

You can use a combination of generate_series and window LEAD function to generate dist_from and dist_to fields and use ST_LineSubstring to cut lines at some interval (here: 1000 m). SELECT row_number() OVER() new_id, b.id, ST_LineSubstring(lin.the_geom, b.dist_from/ST_Length(lin.the_geom), LEAST(b.dist_to/ST_Length(lin.the_geom), 1)) geom FROM ( ...


0

Try this: $sql = "insert into table (geom, name, type) values (st_setsrid( st_makepoint($longitude, $latitude), 4326))"; In double quotes variable substitution is made by PHP, || is the SQL string concatenation operator.


0

Shape file for the public transport network of Vienna wasn't as per one used in the tutorial, it didn’t contain the same amount of attributes which resulted in no result when trying to pass IDs 1 to 3000 through pgr_dijkstra.


3

You can also switch off WFS-T for your service by going to the WMS menu page (Services->WMS) and changing the service level from Complete to Basic.


3

There are multiple ways of achieving this: Use Geoserver's Service Security Connect to the PostGIS table with a user who only has Read-Only access on the Table. Ideally you should do both.


1

You can also use ST_PointN on the exterior ring of your polygon. SELECT ST_Distance(ST_PointN(geom, 1), ST_PointN(geom, 3)) dist FROM ( SELECT ST_ExteriorRing(geom) geom FROM my_polygon_layer ) a;


0

Well I finally made it using array_agg() function after st_dumppoints(): SELECT a.id, st_distance(a.points[1],a.points[2]) FROM( SELECT c.id,array_agg((points).geom) as points FROM (select id, ST_DumpPoints(the_geom) as points from my_polygon_layer) as c where (points).path[2] = 1 OR (points).path[2] =3 group by c.id I don't know if there is a more ...


0

Problem was solved by enabling jsonp in the geoserver


0

You can use ST_ClosestPoint to return the projection of your point on the linestring. SELECT --poi.name, --poi.city, ST_AsText(poi.the_geog) AS poi, ST_AsText(ST_ClosestPoint(road.the_geog::geometry, poi.the_geog::geometry)::geography) AS closest_point, ST_Distance(road.the_geog, poi.the_geog)/1000.0 AS distance_km, ST_AsText(road.the_geog) AS ...


0

change line 92: DROP AGGREGATE aggregatepolygons(geometry, double precision, boolean); to this: DROP AGGREGATE IF EXISTS aggregatepolygons(geometry, double precision, boolean);


1

I had the following message from Boundless: After looking into this more it appears that the security on the VM image is misconfigured. I've made my team aware and they will make the change when they build the v4.9 machine (v4.9 should be out in shortly, then a few weeks after that for the new VM). I've since managed to unlock the root password, ...


0

I think the problem is that your st_intersects mixes a geography and a geometry; I guess that Postgres is trying to cast your geometry to geography, and that fails because srid 2877 is, as your error messages says, not a lat/lon coordinate system. Easiest way is to do everything using geographies and st_geogfromtext (the st_buffer will also draw the circle ...


1

By reading the examples from http://postgis.net/docs/RT_ST_ColorMap.html you should have new line between the two colormap entries in your ' 100% 190 185 135 50% 240 250 150 '. Now it it looks like PostGIS is finding just one entry with 8 elements and therefore the error: NOTICE: More than five elements in colormap entry


2

I would say that it is the order of the filtering that is causing the issues. It would appear that you are doing a GROUP BY geom over the entire table before filtering to the customer. This may also cause other issues if other customers have a geometry at that point as well. Another performance issue may be that you are joining the reduced group back onto ...


1

Try to use ST_GeogFromText instead of ST_GeomFromText select * from circles where st_intersects (towers.the_geog,ST_Buffer(ST_Transform(ST_GeogFromText('POINT(-105.04428 39.74779)', 4326), 2877), 1500));


0

This statement should help: SELECT ST_LineSubstring(geom, ST_LineLocatePoint(ST_LineMerge(geom), ST_SetSRID(ST_MakePoint(-90, 30),4326)),1) FROM roads WHERE id=1234 It returns the partial geometry of a nearby line from a point. Note: The geom in my data is a MULTILINESTRING, so it had to be simplified to a LINESTRING in order to pass into ...


3

Sounds like you are looking for ST_DWithin(): SELECT * FROM points as r LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2); and this to get the average: SELECT r.id, s.id, AVG(ST_Distance(r.geom, s.geom)) FROM points as r LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2) where r.id <> s.id GROUP BY r.id,s.id; ...


1

I figured this out. It seems like there were some artifact functions with the name addGeometryColumn (overloading in postgres is possible). This error in the logfile made me suspect there is something very wrong with the addGeometryColumn function: ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer) is not unique at ...


0

So I've solved this with a Javascript prompt by using the following code: interaction.on('drawend', function(e) { //pass an attribute to the feature var myAttrValue = prompt("Enter Attribute", ""); var myFeature= e.feature; if (myAttrValue != null) { myFeature.set('column', myAttrValue); } pointWFS('insert',myFeature); });


5

You index is on geometry not geography and you are casting your geometry to geography. If you want it to use an index, you need to add a geography index like so: CREATE INDEX gist_geog_berlin_2po_4pgr ON berlin_2po_4pgr USING gist( (geom_way::geography) );


1

st_split(line,point) will produce a collection of geometries which you can then get each st_length. st_Dwithin could be used to locate polygons and then st_distance could be used between the polygons to get the street width. How about this statement which uses st_distance, st_endpoint and st_startpoint to return: the id of the street (I added this so I ...


0

I seam to have found the answer to my own question, using the following SQL from within QGIS using the PostGIS-SQL-Editor: SELECT clipped.* FROM (SELECT pol.*, (ST_Dump(ST_Intersection(ST_Transform(corridor.geom,4326), ST_Transform(pol.way,4326)))).geom As clipped_geom FROM buffer_ger AS corridor INNER JOIN planet_osm_line AS pol ON ST_Intersects(...


1

So finally, here is the solution I used thanks to Barrett ! If it can help someone else here is the code : var defaultParameters = { service : 'WFS', version : '1.0.0', request : 'GetFeature', typeName : 'database:table’, outputFormat : 'text/javascript', format_options : 'callback:callEP', srsName : 'EPSG:4326', }; var ...


3

You could try the following which is a variation of the example from the documentation SELECT DISTINCT s.id, s.clazz FROM berlin_2po_4pgr s INNER JOIN berlin_2po_4pgr m ON ST_DWithin(s.geom_way, m.geom_way, 2000) WHERE m.clazz = '11' and s.clazz != '11';


2

Your subquery is wrong and is probably evaluating to null so that no street segments are close to the null geometry. running the subquery alone would help you identify the problem: SELECT geom_way WHERE clazz = '11' Including the FROM clause to your subquery should return some results: SELECT id, clazz FROM berlin_2po_4pgr WHERE NOT clazz = '11' AND ...


0

What I ended up doing to flip the columns to rows is the following: SELECT p.a_id AS a_id, unnest(array[p.sp, p.mp, p.ep]) AS a_pts, 0 AS b_id INTO TEMPORARY TABLE a_points FROM (SELECT DISTINCT ON (sel_a.a_id) sel_a.a_id AS a_id, ST_Line_Interpolate_Point(sel_a.geom, 0.0) AS sp, ST_Line_Interpolate_Point(sel_a....


3

The clustering approach you're describing is referred to as DBSCAN*. (Yes, with an asterisk in the name). There's currently no functionality to do this in PostGIS, though an enhancement request could be made to add it, since it's only a small modification of the DBSCAN algorithm that's already implemented (unreleased). Barring that, DBSCAN* implementations ...


0

I think you can not specify two geo_fields the way you did in your example. The geo field there is a class property, you can not specify two with the same name. So the second one will override the first one (its a class property). The reason is that the geo-serializer will try to construct a geojson layer, which creates a list of features. This list will be ...


0

Use reasonable buffer value. The largest measure of your polygon is about 0.0007 degrees and you are shrinking it by 3.024 degrees. The result if much less than nothing. SELECT ST_AsGeoJSON(ST_Buffer(ST_GeomFromText('POLYGON((72.89994120597838 19.070245311788284,72.89981782436371 19.069915760688904,72.9002845287323 19.070001951039718,72.9003381729126 ...


1

You are missing pixel dimensions: SELECT ST_AsRaster(ST_Buffer(ST_GeomFromText('POLYGON((-30 40, -20 30, -25 20, -23 10, -30 40))', 4326), 50),100,100,ARRAY['8BUI'],ARRAY[118]);


0

Have you tried st_intersects? Below is a statement using a combination of st_intersects and st_buffer... st_buffer is providing for some error in alignment between the valve and the tube in my sample data. If you really want it precise then remove the st_buffer function. select v.name from valves as v where st_intersects(v.geom ,st_buffer((select t.geom ...


4

You could collect all distinct values for your groups and aggregate them in the db and create a new table. You will get a new field with aggregated strings. And you actually use those for the labeling. CREATE TABLE AS... SELECT string_agg(field_with_distinct_values, '?') FROM table GROUP BY field_for_the_relate You then join this table in Qgis and use in ...


1

You can use UNION SELECT to combine various columns into rows, so from the output of step 2 to stack sp, mp, ep into the same column: SELECT a_id, sp FROM points UNION SELECT a_id, mp FROM points UNION SELECT a_id, ep FROM points You can then use the <-> operator, for finding the closest point in one table to another, used in the ORDER BY ...


2

The following query translates your question into a PostGIS query which sets the default state speed to all roads with NULL speed completely within a state. UPDATE roads SET speed = states.speed FROM states WHERE ST_Within(roads.geom, states.geom) AND roads.speed IS NULL


1

I would say the geometry you're creating should be in SRID 4326 (the point from Google), And you should transform the geometry in 2263 to 4326 to match (or really the other way around), so your query might look like this: SELECT t1.address FROM t1 WHERE ST_Contains( ST_Transform(ST_SetSRID(geometry, 2263), 4326) , ST_GeometryFromText('POINT(-74....


2

Ok I found an answer: You can avoid to get rounded corner by adding the parameter 'join=mitre' to st_buffer: So it work perfectly fine with: SELECT st_buffer(st_buffer(geom,1,'join=mitre'),-1,'join=mitre') FROM mygeotable;


0

A few things b-tree and GiST are different. And since there isn't a GiST index for bytea, then you were probably attempting to build a b-tree on a bytea which will not work. PostGIS has two spatial types: geometry and geography. They can be cast to and from bytea. Perhaps you have a table that looks like this: CREATE TABLE my_table ( id integer ...


2

Default values are accessible via the data provider, so this may work for simple cases: provider = activeLayer.dataProvider() newFeature.setAttribute(0, provider.defaultValue(0))


0

What was message error? If it was this message "UnboundLocalError: local variable 'db' referenced before assignment" You need to change the name of connection to postgres for the same name of Database.


0

Richard, the command st_startpoint returns the first point of a LINESTRING geometry as a POINT or NULL if the input parameter is not a LINESTRING.


3

You will need: 1) A table with LineString geometries: CREATE TABLE lin ( id serial PRIMARY KEY, geom geometry(LineString, 31370) ); CREATE INDEX ON lin (id); CREATE INDEX ON lin USING gist (geom); 2) A table with Point geometries where you want to split your overlapping lines. They can represent train/metro stations, intersections, bifurcations etc. ...


2

In PostgreSQL 9.4+, you can use the json_build_object and jsonb_build_object functions to construct JSON without subqueries, which feels more similar to how this is done outside of PostgreSQL. For example: WITH test_geoms(id, point_type, geom) AS (VALUES(1, 'Park', ST_MakePoint(0,0)), (2, 'School', ST_MakePoint(1,1))) SELECT json_build_object( 'type',...


3

You can create a feature collection by using row_to_json, selecting 'FeatureCollection' as type, and rolling up all you geometries into an array using array_to_json(array_agg(geom)) in a subquery, which creates the appropriate GeoJSON using ST_AsGeoJSON. You can also add the EPSG:4326 as a crs attribute, at the top level of the GeoJSON. When you are ...


0

The link you provided (and other guides) don't go into any detail about setting up your environment paths in the tiger.loader_platform table. Check out this post which has more detail.


4

One possible solution is to use St_ClusterWithin. This creates geometry collections of all the geometries that are within a distince, d, of each other, and takes the form ST_ClusterWithin(geom, d). Start by using ST_ClusterWithin. Wrap this function in unnest and ST_CollectionExtract which takes a GeometryCollection and a number, in this case 1, points, ...


1

As I noted in my comment, try: uri.setConnection("host_name","5432","database_name", None, None, authConfigId = "4zoa91a") This plugin has an example using this method: https://github.com/bstroebl/DataDrivenInputMask/blob/master/ddmanager.py



Top 50 recent answers are included