Tag Info

New answers tagged

1

It's important to understand that you cannot use more than one index into any one table at one time, and that indexes on columns unrelated to queries are of no use. This is why building an index on 'uidn' is of no use on an ST_Intersects query that involves a 'the_geom' column. One thing you can do to improve the efficiency of a spatial index is to ...


0

You can use Spatial Manager Desktop to export from SQL Server 2012 Spatial to PostGIS. There is currently a free beta version ,you can download it here: http://www.spatialmanager.com/downloads/ If you need help on this feel free to tell me. Disclaimer: I work for Opencartis (Spatial Manager developers)


1

Hmm as I recall there should be in an sfcgal output in your postgis_full_version() output. Did you compile postgis with sfcgal support? It's not enough to just have sfcgal installed. I see you are right the instructions in docs don't tell you how to compile with sfcgal support. I'll amend that. What you need to add is in your postgis configure ...


1

I think it's a bit simpler than that. PostGIS natively understands HEX. I think you can do this -- here is a string I got from SQL Server STAsBinary() output (after stripping off the 0x) SELECT '0101000000000000000000F03F0000000000000040'::geometry;


2

I just figured out that one of my earlier approaches would have worked, but I had a typo in my postgres query. Right-click and copy the binary hex string from SQL Server Management Studio. Paste into a string in the PG Admin III SQL Editor window. Delete the "0x" at the beginning of the string. Wrap that value with a decode. Now ST_GeomFromWKB has WKB. ...


5

You no longer need to use the templates or even the script in the new versions of Postgis. As you can see in the Postgis - installation page, all you need is run the CREATE EXTENTION command. -- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- fuzzy matching needed for Tiger CREATE EXTENSION ...


0

I never use it, simply create a new DB from basic Postgresql template: CREATE DATABASE pgis_template TEMPLATE template0; Then i load and run scripts postgis.sql and spatial_ref_sys.sql for your basic setup. I then use this template DB for all the others i create


0

It depends what kind of XML format you have. Do you have GML, KML, GPX or some homegrown XML format? If you are using a standard spatial XML, I would suggest looking at OGR2OGR which is part of GDAL tool kit. It's a commandline tool that can import several formats into PostGIS and other spatial databases and spatial formats. ...


0

You can use dblink in a native Postgres query to split the query up into separate database connections and execute them simultaneously. This is effectively parallelism in Postgres on a single server. It could be mimicked in Python, but I haven't tried it. There are some limitations: 1) the operation needs to be an insert, not an update. Inserts are ...


1

I think all you need to do is zoom in or out in your map canvas. You can also uncheck and check again the render checkbox in the right left corner. Regarding the attribute table you might have to close it and open it again.


3

Found the issue, not related to geometry type. I was JDBC3 driver, replacing it to JDBC4 driver solved it!


2

try to order your result by sequence of your routing table. by doing that you will get your road segments in right order this is what i am doing this: SELECT pt.gid, seq, id1 AS node, id2 AS edge, cost, geom as line FROM pgr_dijkstra('SELECT gid as id, source::int4, target::int4, shape_leng::float8 as cost FROM my_table', ...


0

Make sure that your version of osm2pgsql is built for the version of postgis you have. There were some major changes in postgis 2.0. It might help to run the legacy.sql to add the old functions again, see http://postgis.org/documentation/manual-2.1SVN/PostGIS_FAQ.html#legacy_faq EDIT Second choice: ...


-1

AFAIK the most OSM tools rely on a 4326 projection, so it's not a wise idea to change the DB schema to a different one. Instead you could write a small wrapper or use e.g. Geoserver to reproject the geometries per request.


0

The syntax for osm2pgsql is osm2pgsql [options] planet.osm.{pbf,gz,bz2}. Like most command-line programs, osm2pgsql takes the input file name at the end of the command line.


0

You have to enter the CRS definition into the spatial_ref_sys table of postgis, giving it a unique SRID. Then you can use that in all functions. Usually, all EPSG codes are already in there.


3

SQL Server do not support routing, afaik.


3

I suggest to use a recent version of osm2pgsql. Openstreetmap node numbers have crossed the 2^31 bit boundary for integers, which needed to rewrite almost all software dealing with node numbers. Your max node is just beyond the limit, so i assume newer points get mistreated into a single point at 0°/0° in the Atlantic Ocean.


1

You don't need the API. If you want to be sure you run your query, just work from the table view. From some CartoDB documentation (without a direct link for some reason): SQL in CartoDB To perform an SQL query in CartoDB, first load the table from your Dashboard. Once you have opened your table, in the lower right corner of your table you will ...


1

This is really just a Python question. Use the csv module in Python to write your output (https://docs.python.org/2/library/csv.html) import csv records = [(1, 2, 3), (4, 5, 6), (7, 8, 9)] with open('somefile.csv', 'w') as f: writer = csv.writer(f, delimiter=',') for row in records: writer.writerow(row) print "Done Writing"


0

For one thing, you should generally avoid explicit looping like that in Python whenever possible. Psycopg2's cursor objects support the iterator protocol. This means you can iterate row by row over the results without needing to manually take care of indices. Another thing is that you are calling the execute function many times inside that loop when it ...


0

You must understand that load balancing works for high-traffic sites. Your problem seems to be related with the fact that tiles are created on the fly from the DB, instead of created on the DB once and then being cached in your server. Perhaps adding a caching solution for tiles might help: Check http://giscollective.org/tutorials/web-mapping/wmsseven/ for ...


0

We have a comparable system (a research project); we're aiming for a 20TB Postgresql database holding coverage data such as Remote Sensing imagery. We're running this on a PowerEdge R720xd Rack Server with 128GB of RAM. Using CentOS as the operating system. Our other software is MapServer, Rasdaman, Apache httpd web server, and Apache Tomcat. We haven't ...


3

First, never swallow an exception, or catch all exceptions. Your connection code should be more like: import sys import psycopg2 conn = None try: conn = psycopg2.connect("dbname = 'routing_template' user = 'postgres' host = 'localhost' password = '****'") except psycopg2.DatabaseError, ex: print 'I am unable to connect the database: " + ex ...


0

There’s a thorough explanation in the PostGIS FAQ: In PostGIS 2, the default geometry operator class gist_geometry_ops was changed to gist_geometry_ops_2d and the gist_geometry_ops was completely removed. Simply remove the gist_geometry_ops statement, changing this: CREATE INDEX idx_my_table_geom ON my_table USING gist(geom gist_geometry_ops); To ...


4

Just change the second line RETURNS geometry(Point, 4326) to RETURNS geometry The "type modifier" is a 2.0 feature. You might have other problems if GeoAlchemy expects PostGIS 2.0+, but for this particular function, just removing the typmod syntax should suffice.


1

My everyday work is done using pgAdmin and when I work with "cloud" server I connect with SSH remapping port to it and open database always with pgAdmin.


3

According to www.postgis.org/docs/ST_Collect, ST_Collect — Return a specified ST_Geometry value from a collection of other geometries. (emphasis mine) but it seems your location column is of type geography. Perhaps you need to convert the geography to a geometry via casting: St_Collect (location::geometry)


0

Try this instead your INSERT: INSERT INTO spc_clip (geom) SELECT (ST_Union(a.geom, b.geom)) FROM spc as a, simple_us as b;


0

SMALL EASY WAY IS TO NOT TO LOAD DATA WITH POSTGIS WICH U INSTALL WITH POSTGRES BUT TO EXPORT SHAPES DIRECTLY FROM QGIS ......


0

If you need transform from Polygon to Multipolygon you can use st_multi function. http://postgis.net/docs/ST_Multi.html


0

If you are learning PostGIS using a Tutorial based on PostGIS 1.5, when the version number is up to 2.1 is a mistake. (Unless you are using 1.5) This command shp2pgsql is a bash command that's pipe'd into your postgresql shell shp2pgsql -c -D -s 4269 -I tl_2010_11001_tract10.shp dc_census_tracts | psql -d dc-census


4

What do you mean by line = length? I guess what you mean is that your subquery will find the id of the shortest line. Then you ask in the outer query for a line with that id and some number less than 500. If the shortest line then have a a number that is more than 500 you will get nothing back. EDIT Something like this should work if I understand you ...


1

You don't need the ST_ClosestPoint function. ST_ClosestPoint only gives you the same point that ST_Distance calculates and uses internally. So what you are doing is running the same algorithm twice. If I don't miss something or just think wrong about it you are also doing things twice when you are both grouping and using distinct in this way. Distinct ...


2

With the advice of Mapperz I've edited my query: SELECT DISTINCT ON (lnumber) lnumber, strahler, min(ST_Distance(ST_ClosestPoint(strahler_streams.geom, landslide.geom), landslide.geom)) AS distance FROM landslide, strahler_streams WHERE ST_DWithin(strahler_streams.geom, landslide.geom, 2000.0) GROUP BY lnumber, strahler ...


0

Well, the_geo_webmercator should be the_geom_webmercator, before that gets you. The rest, you'll need a way to pull a single cartodb_id instead of trying to group them. A simple way is just to grab the min one, something like this, Min(cartodb_id) cartodb_id. So I would do, SELECT address, string_agg(name || ', ' || profession, '; ' ORDER BY name, ...


3

Your question probably requires a much more complex answer than this, but I'm just going to say that you may be able to use the following query to get the data you are looking for, to use the ST_OffsetCurve method: SELECT ST_OffsetCurve(theGeom, 2, 'quad_segs=4 join=round') AS theOffset FROM ( SELECT (ST_Dump("Shape")).geom AS theGeom ...


1

As noted by Stoner, you are using Postgres point data type instead of PostGIS geometry(point) and geoserver doesn't work with Postgres point data type. If you are using PostGIS 2.1, a new feature introduced is casting from Postgres Type to PostGIS type. You should be able to do this with the command ALTER TABLE realestate ALTER COLUMN coords TYPE ...


2

To better understand your problem, first I have imagined the bellow situation: And these are the lines and the polygons tables: Also, I've tried to visualise the result, based on your requirement: to find out which line is how long within a surface: The above images helped me to build the necessary query: SELECT a.id_0 gid_line, a.name line_name, ...


7

If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed. On the other hand psql is sometimes your only option when ...


1

So answer to question, no there is no way that i know do to do this in QGIS without creating custom python plugin (but i know postgresql better than qgis) for postgresql, here some tips CREATE OR REPLACE VIEW lines_touching_buffer AS SELECT * from lines l WHERE l.geom && ST_GeomFromEKWT(insert your ewkt here) ; That creates view which displays ...


1

Postgis way : (import polygon shape to postgis using shp2psql ) UPDATE points SET district = poly.district FROM poly WHERE ST_Within(points.geom, poly.geom) see ST_Within and i cant remember easy QGIS way , but its possible


4

Your problem is that you have added your data using Postgres data type Point, http://www.postgresql.org/docs/9.3/static/datatype-geometric.html Rather than the PostGIS geometry, or Geography type http://postgis.net/docs/reference.html#PostGIS_Types GeoServer has a PostGIS Data Store connector, rather than a Postgres Data Store connector. Good Luck


2

I think if you set up a PostGIS view that created a buffer on points in the layer you're editing in QGIS, and then queried the vector features the layer you're trying to query, you could do this. A great example is the blog post and video by Tim @ Linfiniti found here


-1

mmrs151. To upgrade from proj version 4.7 to proj 4.8 on Ubuntu 13.04. At least it helped me solve the same problem. $ sudo apt-get upgrade libproj-dev


3

Reason is that your wkb_geometry is "well known binary" in bytea type of column, it should be geometry type. You can convert it to geometry using ST_GeomFromWKB(bytea) see this , if you dont want add new column then replace ST_ZMax(wkb_geometry) with ST_ZMax(ST_GeomFromWKB(wkb_geometry))


0

One way is re-use this answer this returns start and endpoint for line , use negative offset for other side SELECT ST_StartPoint(ST_OffsetCurve(center_geom, startpointoffset) as start, ST_StartPoint(ST_OffsetCurve(center_geom, endpointoffset) as end, from xx where yyy this makes line SELECT ST_Makeline(l.start, l.end) FROM (SELECT ...



Top 50 recent answers are included