New answers tagged

1

You can run any SQL query from the SQL tray on any layer or map visualization. In your case I'd do: Create a new column of type date on your table Run somethingl ike UPDATE mytable set mydatecolumn = to_date(stringcolumn,'MDDY') Remove the string column when you are sure your date column is correct Alternatively you can use the SQL API from any client ...


1

The pg_trgm extension should already be available with PostgreSQL 9.5 on Windows (supposing you installed via the official installer). This means that there is no need to install it, however you need to activate it in each database that need to use it. The only thing left to activate it should be to run the following SQL code: CREATE EXTENSION pg_trgm; ...


3

The "same location" part of your question is problematic, since locations are stored as floating point values, and due to computer operating system rounding errors you will get two points that you consider at the same location with very slightly different X,Y. The correct way to do this is to define a buffer distance within which you consider two points as ...


0

I was waiting that the moderators will unhold your question here after the edit but still nothing so here is a possible answer, check the comments I left there for adding the count of overlapping points: Here to add the columns of X and Y : alter table your_Table add column x double precision; alter table your_Table add column y double precision; update ...


4

As you discovered by trial and error, there were few nagging issues you needed to fix, the last of which was resolved using ogr2ogr's -nlt GEOMETRY* argument. * Note the recommendation in @LeeHachadoorian's comment that -nlt PROMOTE_TO_MULTI be used as a default solution, rather than nlt GEOMETRY, as the former promotes best practice in addition to ...


1

Essentially my project was to loop through 7 folders: 1-7 representing days and in each folder there were thousands of csvs containing pickup and dropoff locations. You will have to mess around with your path names. I also never parsed GPX files, I am not sure how different that is from reading csvs. But this script should give you a decent understanding of ...


1

So the answer seems to be; it's not possible. ogr2ogr does not pick up on xlinks in GML files. My solution was to use Safe FME to load the data instead.


3

You must first UNION all the geometries. Then you can LEFT JOIN the geometries ON ST_Equals to retrieve the values. Sample code -- Create dummy test values WITH sand1 AS ( SELECT val, ST_MakePoint(x, y) geom FROM (VALUES (11, 0, 0), (22, 1, 2), (32, 3, 1)) a(val, x, y) ), sand2 AS ( SELECT val, ST_MakePoint(x, y) geom FROM (VALUES (12, 0, 0), (23, 1, ...


3

The package PostGIStools can help. See for example the vignette. Another way could be to transform your Spatial*DataFrame geometry to WKT, insert into PostGIS using the classic RPostgreSQL package and re-create the geom there.


1

shp2pgsql is a command-line program. You need to open a Windows command prompt and type "shp2pgsql". If the PATH is correctly set up, it will print out some instructions. If not, you'll get an error. There's some talk about setting the PATH on Win 8 here. The path should point to the folder where you found the shp2pgsql file.


0

If you would like to get distinct geometries, use in postgis: SELECT DISTINCT your_geom_column FROM your_table; If you are interested in the number of points at the same positions: SELECT your_geom_column, count(*) FROM your_table GROUP BY 1;


2

I think first of all you'll have to split the csv-files you bought in order to generate 'small' shapefiles of valid size. Then possibly it is a suitable approach for you to load your shapefiles into a PostGIS database and do the merge in the database? Thus you shouldn't have anymore problems with file size an the processing would be faster too.


1

ShapeFiles have size limits: .shp maximum 8gb The other parts, for example .dbf do not, but are recommended to not exceed 2gb. http://www.gdal.org/drv_shapefile.html


2

Another way to approach this, that may give you some flexibility for the longer term, is to create a view based on your query in PostgreSQL, then load that into QGIS with the standard PostGIS data loader. Basic SQL Query in Postgres: Create View from SQL Query, with unique ID Load View in QGIS Unioned layer displayed in QGIS The benefit to saving the ...


2

PostgreSQL / PostGIS (pgAdmin3) create a view and use ST_GeomFromText (text WKT): text WKT = geo;


3

You can execute SQL queries and load results as layers in QGIS using the DB Manager. If you still want to go through csv you must take care that WKT-geometry is included in the csv. You can make the query and save the result to CSV with ogr2ogr using the following command: ogr2ogr -f CSV -lco GEOMETRY=AS_WKT -sql "select avi, st_union(geom) geom from ...


0

I recently setup a web mapping application with OL 3.15, calling a WFS layer published by mapserver 7.0, which uses PostGIS based data tables backend. Here are the relevant parts of the mapfile: CONNECTIONTYPE postgis CONNECTION "dbname=water host=127.0.0.1 port=5432 user=water_dba password=<your-db-password> " DATA "geom FROM ...


0

First. I am not very familiar with MapServer but according to it's documentation, In order to enable MapServer to serve WFS, it MUST be compiled against certain libraries Second, you have to configure a PostGIS connection in your MapServer, documentation for that here. Third, you will have to create a mapfile to describe the layer to MapServer. ...


0

From your screenshot I see that f_table_catalog field is blank. Mine has the database name in that field & if I delete it I get the same behaviour you are describing.


1

You could use a subquery and ST_Union select ST_AsTexT(ST_Union(a.singlegeom, b.singlegeom )) from ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM PUBLIC .mzones WHERE ID = ALL (ARRAY [ 'GMZ730' ]) ) a, ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM counties WHERE fips IN ...


0

Given your 0% match combined with your mention of digitized historic maps (big props to you for putting in the work required to represent change over time!!) it's likely that you need to link some additional attribute data to your roads before any sort of geocoding will work. every road segment, for example, in the u.s. census bureau's road network is ...


2

First thing you need to do is to set your date field correctly as otherwise you canĀ“t order it. Something like this(refer to the documentation for the correct time format): alter column time TYPE timestamp USING time::timestamp without time zone Or if you want to have directly in your query without changing the column you can use the CAST function. Then ...


2

PostgreSQL partitioning is only going to make things faster if you have a non-spatial constraint in your query and that constraint is applied to each child table in the partition via constraint exclusion. (The constraint exclusion config also has to be turned on.) Partitioning will be useful for performance, if you have a key you can build constrained ...


5

By using CTEs you are forcing the planner to evaluate each CTE component without looking at any other part of the query. Once you've extracted the results from the tables using the CTEs the fact that there is an index is lost to the planner. You've created a new, non-indexed relation in the CTE. In general, if you can write logic as a plain join, always do ...


1

Not sure what do you mean with "a house next to it" but here an example of what you can try: SELECT a.id, a.geometry, 'T'::text as type FROM houses a, houses b WHERE ST_Intersects(a.geometry,b.geometry) AND a.id != b.id Could be done with other spatial operator (ST_DWithin could be a better candidate). Better with Gist index on geometry field.


0

This line causes the output to be directed to the screen postp.pipeOut = true please see this link for more information.


2

There's two pieces to the performance pie here: The time to calculate the tagging, which you should break out by doing some test runs with things like CREATE TABLE tract_pt_counts AS SELECT Count(*), tract_id FROM tracts t JOIN pts p ON ST_Intersects(t.geom, p.geom) GROUP BY tract_id The time to update a table in place. This is actually probably a lot ...


1

The first step should be to enter your data in a PostGIS database. Then you have to connect MapServer to PostGIS and create layers for each of your tables or views in the PostGIS db. Finally you will have to load these layers in OpenLayers.


3

Assuming you have two tables "a" and "b". Table "a" is the table to be cut. CREATE TABLE a ( id serial PRIMARY KEY, geom geometry(MultiPolygon,31370) ); Table "a" is of type multipolygon because we don't know yet if some polygons will be separated in multiple parts afther the "cookie cut". CREATE TABLE b ( id serial PRIMARY KEY, geom ...


5

ST_Area will use the units in your coordinate system, which as you say are degrees. So you have two options: transform your geometries to a coordinate system that uses feet or meters cast your geometries as geographies. ST_Area will then perform the calculation in meters. You then need to convert from square feet or square meters to square miles. Your ...


0

To be clear, it seems that you want to count routes by county. I assume routes is a (MULTI)LINESTRING and counties is a (MULTI)POLYGON. You have also indicated that they are both tables use the same SRID (4269). The ERROR: relation "counties" does not exist is odd, but you say in the comments that SELECT count(*), name FROM public."Counties" group by name; ...


1

This query can be improved in a number of ways. As was pointed out in comments, ST_DWithin(ad.geom, street1.geom, 30.0) OR ST_DWithin(ad.geom, street1.geom, 50.0) is just going to return everything within 50m, because everything with 30m is also within 50m. If there is something you were trying to accomplish other than joining all streets within 50m, you ...


0

I don't know why this actually works now, maybe someone could explain it, but after many tries I succeded by using the following code: SELECT AddGeometryColumn ('vehicle_2015_02','projected_point',4326,'POINT',2, false); UPDATE vehicle_2015_02 v02 SET projected_point = ST_LineInterpolatePoint(romo2015_way.st_makeline, ...


0

You may need to rewrite your UPDATE statement, using an explicit join. UPDATE vehicle_2015_02 v02 SET projected_point = q.newpoint FROM ( SELECT vehicle_2015_02.objectid, ST_LineInterpolatePoint( romo2015_way.st_makeline, ST_LineLocatePoint( romo2015_way.st_makeline, ...


1

This is WKB format wichs is a binary representation of the geometry. To select the geometry in GeoJSON format for example you can use : ST_AsGeoJSON()


1

Read your CSV file and create a vars_list list of tuples to insert. Reading the CSV file is off topic, but it should have a structure something like this: vars_list = [ (lng1, lat1, lng2, lat2), # first record (lng1, lat1, lng2, lat2), # second ... ] Then insert them all at once with executemany, like this: sql = '''\ INSERT INTO ...


1

The error message and specifications are pretty clear: GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon. PolyhedralSurface is not in that list, so you'll need to convert it to another geometry type.


2

You can do your own function in PostgreSQL like this (Example taken from the docs) : CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM ...


0

Simple answer: in the instances, just type: localhost, 5432 Or change to your port number it might be 54321 depending on your installation Colon and semi-colon doesn't work! comma is the answer. I just did it now.


0

I was struggling with the same problem today. Finally got a solution that I am sharing here. The root of the problem is search_path of postgres database. sudo su postgres psql database_name the check out the search_path of the database database_name=# show search_path your schema that has the postgis extension should be in the search_path. To check ...


2

Using views (as suggested in link) is pretty easy, for example. -- population table (the table you edit when changes...) CREATE TABLE pop_table ( gid integer primary key, population integer, geom geometry); -- Density view (you can open it like a table in QGIS) CREATE VIEW density_table AS SELECT gid,population,(population/ST_Area(geom))::float AS ...


-1

I think the package postgis from the ubuntu repo will install everything you need, as explained in the last lines of https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt


2

Replace ST_GeomFromText('POINT(715127 957625)',4326) with ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(715127 957625)'),SRID_YOU_ARE_USING),4326) 4326 is the SRID of WGS84, which is used by GPS, and is the commonly used lat-lng system you're looking for.


2

The reason for the error is most probably that the geometry type of the WFS feature type is defined to be MultiLinestring or MultiPolygon, but QGIS it trying to insert simple Linestring or Polygon. You can check the WFS geometry type by making a DescribeFeatureType request. For example ...


7

Overlay operators like Contains, Intersection, SymDifference, etc. require exact noding. This is because there are floating point differences on the order of 1e-14 when interpolations are performed between two lines that are not equal. And while 1e-14 is a tiny number, it is still not zero. Use ST_Snap to help "snap" vertices from one geometry onto another ...


1

There are two views that you need to check geometry_columns and geography_columns that will provide you with a list like: "ian";"public";"coastline";"geom";2;27700;"MULTILINESTRING" "ian";"public";"motorway";"geom";2;27700;"MULTILINESTRING"


4

At the scale of your display, these lines appear to be coincident, but in reality they're not: I've exaggerated the difference between the two lines using the "Magnify Topology" tool in JTS TestBuilder, which I highly recommend for looking into cases like this. Depending on the goal of your analysis, it might be suitable to snap your vertices to a grid ...


0

The solution seems to be using the "Value Relation" widget as the edit widget in the fields tab of the layer properties. I set the layer to my lookup table, the key value to the column containing the encoded integer column, and the value column to the string value column. Then I had to create a filter expression to match all the columns I require: ...


0

The easiest way (in my opinion) is to use GeoTools, if you follow the quickstart tutorial you will see how to draw the features of a shapefile on the screen using swing. Then all you need to do is change the input datastore to use PostGIS (a simple change to the parameters) and you are done.


1

Updating is often quite slow. If it is possible, it is probably a lot faster to recreate the whole table like create table new_table as select pnt.*, poly.name from point_table pnt inner join poly_table poly on st_within(pnt.geom, poly.geom); I do this a lot and rename the new table to the original name and recreate indexes and constraints on it. If it ...



Top 50 recent answers are included