Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
5

From the documentation of ST_GeomFromText, if you use MULTIPOLYGON, your SQL in PostGIS lack of brackets as part of a multipolygon is into two brackets. Here is a working code: SELECT ST_GeomFromText('MULTIPOLYGON(((-115.528984981578 51.2006109579736, -115.526434407604 51.1974145241941, -115.546464451 51.1829871710806, -115.550742857 51.1811679580806, -115....


3

profile_type_id is not in your select statement so the outer select can't see it. You need to try something like: CREATE TABLE cross_section_lines AS SELECT st_makeline(geometrypoint) as cross_section, cross_section.profile_code, cross_section.administrative_area_id, cross_section.profile_type_id FROM (SELECT ...


3

The query is taking a long time because it is doing a cross products between tables a and b (and poly). So if poly has 1 polygon only, and both a and b have 1,000,000 features, you end up computing st_DWithin 1,000,000,000,000 times. You can try to combine the two update using a join instead. You may have to use a subquery to achieve it though, as ...


3

To be noticed: Area and length measurements in EPSG:4326 and do not give reasonable results but that's dealt with many other quesions https://gis.stackexchange.com/search?q=epsg%3A4326+area+ Re-ordering the multipolygon members by area can be done by exploding the multipolygon with ST_Dump, ordering the primitive polygons by the result of ST_Area and ...


3

From the examples in the plpygis documentation, it gives : Some functions that analyze or manipulate geometries are possible in SQL but are easier to model in a procedural language. The following example will use Shapely to find the largest component polygon of a multipolygon. CREATE OR REPLACE FUNCTION largest_poly(geom geometry) RETURNS ...


3

I'm presuming "budivly" is your land plots and "dilyanki" is your buildings, and that you have an "id" column as a primary key. I've not tested this, but it should get you started. EDIT to add option to display count: CREATE OR REPLACE VIEW dvi_budivli_view AS SELECT budivly.id, budivly.geom, COUNT(dilyanki.id) -- Optional FROM budivly JOIN ...


2

Since you don't want the column in the GROUP BY section, you would have to aggregate the values of this column. For instance, you can use STRING_AGG and possibly keep only the distinct values via the DISTINCT keyword. You may have to cast the column to text as well SELECT id, string_agg(distinct colA::text, ',') FROM mytable GROUP BY id;


2

Not 100% sure this is what you're after, but executing a basic from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from geoalchemy2 import Geometry from sqlalchemy import Column, Integer engine = create_engine( 'postgresql://user:pwd@host/db' ) Base = declarative_base(...


2

Parameters passed to ogr2ogr flags are themselves parsed as string literals (and/or parsed to numeric types if needed); they don't require string enclosing (' or "), rather, consider them always getting enclosed implicitly. To explain the behavior: -select col1, col2 implicitly translates to "col1", "col2" thus -select 'col1', 'col2' will translate to "'...


2

(Wrote a comment about this and the OP said it was right, so I will put it here for other people to read as a answer) About the point that causes the different result: is it clearly inside the polygon? Is it right on the border of the polygon? I have had this issue sometimes with points that are right on the border of the polygon. For some reason, when ...


2

It looks as though shp2pgsql is not recognised. You may need to either A) Run with the full path to the folder containing shp2pgsql.exe in front of shp2pgsql in the command OR B) You need to make sure that the shp2pgsql.exe is in a folder that's on the PATH environment variable


2

If you want to login as a user other than the default you must tell psql. Please read the documentation. psql -U saifeddine -d wchiba will log you into Postgresql as the user saifeddine and connected to the wchiba database. However this has nothing to do with loading a shapefile, so you probably want shp2psql which also takes a -u username option, see ...


1

1) reordering the wrong geometry type multipolygon can not be! 2) reorder them... SELECT id, area, ST_CollectionExtract((ST_Multi(ST_Collect(geom))),3) geom FROM source_table GROUP BY id, area, geom ORDER BY id, area, geom DESC; P.S. So let's summarize. Together, through my presentation we get the following picture. 3) Arguing further, using this set ...


1

You want ST_ENVELOPE as in SELECT 'place1', st_asText(ST_Envelope(ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))',4326))); Gives POLYGON((-71.1776820268866 42.3902909739571,-71.1776820268866 42....


1

To use another user, add the parameters -U myusername -W -U specifies a username, -W will prompt for the password


1

You can solve this issue by executing the following query in pgAdmin CREATE EXTENSION postgis; Hope this answers your question.


1

Use Postgis 2.0 Shapefile and DBF loader exporter.


1

It worked! Thanks ThingumaBob. For reference the code I used: CREATE FUNCTION check_intersect(gm GEOMETRY) RETURNS boolean AS $$ SELECT EXISTS ( SELECT 1 FROM afrivs AS tb WHERE tb.geom && gm AND ST_Intersects(tb.geom, gm)); $$ LANGUAGE sql ALTER TABLE weirs2 ADD CONSTRAINT check_intersection CHECK (...


1

OP based on your image, your replace() function is trying to match something like.. Puerto Rico ..which is the value in the database, with.. (Puerto Rico) ..which is the value you're telling it to match in the second argument of your replace() function. Remove the ( and the ) from the second argument in your replace() calls, so they look like this.. ...


1

The problem seems to have been caused by adding layers at different times. If I add a layer to an existing project I get prompted for credentials for each time layer(s) are added. If I start a fresh project and add all the layers at once I only get one username/password prompt.


1

Cadcorp have just informed me that the current release of SIS 9 doesn't actually support PostgreSQL 11 which will more than likely explain the issues. I have been using SIS 9 with an older database successfully, so this certainly ties in with everything I have tested. They have said PostgreSQL 11 is due to be tested with the next service release (9.1)


1

Go to C:\Program Files\PostgreSQL\10\bin\postgisgui (or wherever the loader is installed). For example, for me, C:\Program Files\PostgreSQL\11\bin\postgisgui. Make a copy of the libintl-8.dll and paste it in the same directory. Rename it, libintl-9.dll (with the number 9) instead of libintl-8 - Copie.dll Now, re-run PostGIS 2.0 Shapefile and DBF Loader ...


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