Tag Info

New answers tagged

0

It kind of depends on you needs and dynamic nature of your points and buffer. If the buffer is dynamic I would store the point and make a view creating the buffer. If the buffer is static, you could store it as a polygon using ST_BUFFER(). Either way you can always extract the other.


0

Without reading all the details I think: You should use ST_DWithin instead You should cast to geography type, or just use the geography type instead of geoemtry type which makes ST_DWithin work with meters instead of degrees So your query will be something like: SELECT ST_DWithin(ST_GeogFromText('POINT(28.239418677985668 -25.75443387513752)'), ...


1

I was able to create the correct answer, so have posted the correct Query here for future users who want to convert GIS data from two joined PostGres Table's to geoJSON I used information from the following pages to help me solve this: http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql ...


0

You can create a database view which joins the spatial and non-spatial tables. You need to add a unique id column to the view definition to enable QGIS to load it.


2

You're getting an error because: activations.activationid is in a subquery that's been aliased to zz. Since the subquery doesn't really do anything you can flatten it and get INNER JOIN hat.activations.activationid zz ON (zz.activationid = ... However projectsgeom is on a diffrent nesting level too so the above will only change the syntax error. Either ...


0

GROUP BY for geometry uses bounding box. I'm not absolutely sure about geography but I suspect so too. The bounding box is generally a little larger than the object. That said, I wouldn't be using geography or geometry as my primary grouping column. You are almost guaranteed to get the wrong answer given a large enough sampling of data. I am a bit ...


0

QGIS does more work to load a view than it does to load a table. "Viewing" a view in any database viewer like pgAdmin is also slow - it's not a QGIS thing, it's a view thing. My data doesn't change much - about once a year - so I only use tables. I just don't find I need the abstraction that a view provides. (Good discussion here ...


2

It's a good bet that your SRID is 4326, and that you are testing if each geometry is within 500° Latitude and/or Longitude, which is a non-sense distance. Since everything is within 500° of each other, the expected return is 1560 * 1560 = 2433600 rows. Try casting to a geography type, which uses distance units of meters (e.g., see ST_DWithin for geography ...


3

According to the documentation (chapter 7.3), there are several geometry accessors which might meet your criteria, depending on whether the geometry is a collection, a polygon, or a multi-polygon: ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL. ST_NumInteriorRings - Return the ...


3

Use the geometry accessor ST_NumGeometries. For example SELECT ID, ST_NumGeometries(shape) FROM myTable If your geometries contain geometry types other than polygons, you will need to break them up and keep only the polygons before counting. But from your question this would not be required.


0

When you create a PostGIS database using create extension postgis the "spatial_ref_sys" table is created automatically. I don't see any use case where you would want to do that by hand. If you are new to the technology, you really need to read some basic literature. Just asking about random details here won't get you anywhere. See for example Getting ...


0

I am not very exerienced, but I would try function geometry ST_MakePolygon(geometry outerlinestring, geometry[] interiorlinestrings);


0

I'm sure this function is incredibly inefficient (not to mention that it's coded very poorly -- it was written as a "once-off" quick and dirty implementation), but here's one whose results work quite well for me: CREATE OR REPLACE FUNCTION public.calc_colors( tbl text, unique_field text, neighbour_style text, search_distance real DEFAULT 0) ...


3

Put your first query in the from clause of the 2nd: something like that should work: SELECT building.id building.address FROM building, (SELECT event_point.id, event_point.geom, st_buffer(event_point.geom, 100) AS buffergeom FROM public.event_point) buffer WHERE ST_within(building.geom, buffer.buffergeom)


1

From the ArcSDE Administration Command Reference: Beginning with ArcGIS 10.1, you must use a geoprocessing tool or Python script to create a geodatabase. For geodatabases in Oracle, SQL Server, and PostgreSQL, if you can use the default settings, use the Create Enterprise Geodatabase tool or a Python script that calls this tool. If you want to ...


0

Thanks for your answer! The code is working but the access to the secound layer is missing (landkreise_projected_deleted). How can I access to the secound layer?


0

You need to start editing in order for the versioning buttons to be available. Go to Editor toolbar and "Start Editing". When you are finished with Reconcile and Post, Save and Stop Editing.


0

Another alternative is to use GDAL ogr2ogr utility. If all your csv are in one directory this terminal statement (using OSGeo4W Shell) should work: ogr2ogr -f "PostgreSQL" PG:"host=myserver.abc.com port=54321 user=postgres dbname=mydbname password=mypassword" C://Temp


0

In Linux/Mac or on Windows using cygwin you can simply call the psql command in a loop, using Postgres's built in COPY command. You use the -c switch to psql to indicate that you are running the command inside the quotes. This works well because you can use the operating system to get the list of files very easily. For example, for x in $(ls ...


1

If you want to insert into three tables, added, deleted, unchanged, then there is no need to return anything from the trigger, just do this work inside the trigger. CREATE or REPLACE FUNCTION cd_3() RETURNS trigger as $$ BEGIN INSERT INTO added (geom) SELECT ST_Difference(new.geom, geom) FROM public.landkreise_projected_added ...


0

You need to enable the command-line tools to work from your shell sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/shp2pgsql sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/raster2pgsql


2

Here's one approach to designing your database tables: Wherever you have shapes that have exactly the same combination of geometry type and set of attributes, put them in one single table. Probably, this will be based on those themes you mention. If that still appears to be too many tables, maybe think about combining those themes that are very similar (re. ...


0

Oh well I totally forgot about this question. I in general did what you said, but without a function. Also I changed to approach to the problem slightly to gain much better performance. So instead of looking for the filling stations each time I need to refill in my simulation, I query all available filling stations at the beginning using the Query below. ...


3

I believe the general outline (partly tested so far) is: Find the points representing stream sources, as in this answer. Intersect with the polygons table to get a count of source vertices by polygon. Use ST_DumpPoints in conjunction with group by geometry to get a count of each point. The idea being to get a count of how many rivers meet at a given point. ...


1

Desktop is a 32bit application, thus 32bit client libs are required when connecting to the database. ArcGIS Server is 64bit. The 64bit client libs need to be installed for the service to work. The particular help link for Postgres client libs can be found here


1

You could write a pl/pgsql-function in which you create a temp table(which you can drop on commit).then loop through the records of your (executed) sql-statement and write one result during each loop in this table.if all loops are done execute a "select * from temptable" and set the result of this query as return value.


1

Answering my own question here in the hope that this helps others (or my future self). In the intervening weeks since I posted the question, I was unable to find an elegant solution to this problem that could be done algorithmically in PostgreSQL. Instead, I broke the task down into constituent parts and more or less brute-forced it. For the purposes of ...


1

I've had good luck using the 6-color algorithm described in the introduction to Two Linear-Time Algorithms for Five-Coloring a Planar Graph. Although it's certainly possible to color a graph using fewer colors, it may not look any better than using 5 or 6. ALGORITHM 6-COLOR. Given an n-vertex planar graph G in adjacency list form, this algorithm ...


0

In pseudo code, this should work: select all from polygons (FULL OUTER?) join with points on polygon intersects points (FULL OUTER?) join lines where polygon intersects lines were line.riverid doesn't equal point.riverid group by polygonid count (pointid) > 0 I'm not really sure how to build the query, and I can't test it without a database to test on. ...


0

To add values to a datebase on the fly, break it up in three steps: 1) Catch the click and position in Javascript: https://developers.google.com/maps/documentation/javascript/examples/event-simple 2) Send the position to a server using Ajax: http://api.jquery.com/jquery.ajax/ 3) Save the values in the database using PHP: ...


2

You need to use ST_Transform. For example, taking a point somewhere in Southern India (which I'm guessing from the UTM zone and your profile is the area you are interesting in) SELECT ST_X(geom), ST_Y(geom) FROM (SELECT ST_Transform(ST_SetSrid(ST_MakePoint(627516, 1413990), 32643), 4326) as geom) g; or in your case, if you have a table already with ...


0

Osm2PgSql will only import polygons into the planet_polygon table if they are closed. You may find the missing data in the planet_lines table Use OverPass Turbo or OSm to get the osm_id of the missing geometry and search for it in the polygons and lines table's. This will tell you if they were actually imported. If they are not in those tables, they were ...


4

I suspect we don't support it, however you could try with the -m switch where what follows the -m is the filename consisting of old name and new name. It might not work though since gid is an autogenerated field name. If it doesn't feel free to put in a ticket in our ticket tracker http://trac.osgeo.org/postgis/newticket . i think it's a fairly easy ...


1

Replace the geom for the_geom (note the underscore). If the column really includes a space then you must reference it with double quotes: "the geom". This later case is not such a good idea, I would advise you to rename the table column to the_geom.


2

If I understand your question correctly, I think @gcarillo has the right idea in his comment. Rather than building the linestrings before the intersection, determine the points inside the polygon first and then build the linestrings based on sequence groups. Although it looks a little complex, the following query will achieve this. I've made some ...


0

Here is one option: Merge all common shps together in ArcGIS or QGIS (preferred to merge to gdb feature class) If you have QGIS installed you may use ogr2ogr command using OSGeo4W shell command utility to load to your pg database, e.g. For single table(s) in gdb ogr2ogr -f "PostgreSQL" PG:"host=serverName.xyz.com port=54321 user=userName dbname=mydbname ...


-1

last time i have same problem : what i have done to solve that is refer on this link http://hub.qgis.org/issues/735 i hope can solve your problem to. the problem is because postgres still limit connection. if you have add configuration for truth the localhost for connection to database everything running smooth. Other thing you must consider after have ...


2

=== Work on My Mac === I hope this can solve your problem to. The problem because Qgis Connect with TCP / IP protocol, refer to this link : http://hub.qgis.org/issues/735 QGIS uses tcp/ip connections to PostgreSQL, regardless of whether both client and server are on the same host. You can configure PostgreSQL to accept such connections as trusted, however ...



Top 50 recent answers are included