Tag Info

New answers tagged

0

Since you are searching for identical points only, the bounding boxes intersect operator "&&" will be most efficient. In case your tables don't have spatial indizes, create them: CREATE INDEX ON r USING gist (geom); CREATE INDEX ON e USING gist (geo); Then for each linestring lookup the matching endpoints and the bigger km value: UPDATE r SET ...


1

At this moment CartoDB only offers a single API key per user which as you say, is full control.


4

In Postgres column and table names are not case sensitive and everything is converted to lower case before executing the query, see the docs. Therefore, SELECT SomeColumn FROM SomeTable becomes SELECT somecolumn FROM sometable unless you write it as SELECT "SomeColumn" FROM "SomeTable" In my view this is messy, especially when dealing with SQL ...


0

well the winner is to not use the = sign. this works for anyone looking.. ogr2ogr -overwrite -t_srs EPSG:3978 -f "ESRI Shapefile" -dialect SQLite -where "GIVEN_CLASS LIKE 'CLAS% A" dst src indicating dialect + using -where + Like operator.


2

You can do "update .... where rowid = N", but that is all. A single specific record or the whole table/selection.


4

You should be able to obtain logs from Postgresql once you configure it. I typically will set log_statement = 'all' in my postgresql.conf, even on production databases (with a separate cleanup routine to clear them out every 2 weeks). Also change the log_line_prefix to something that includes at least the process id, something like: log_line_prefix = '%t ...


2

You need to cast the string to a geometry then set its SRID to match the geometry column's SRID. select id from database where point_lat_lon = st_setsrid('POINT Z (116.309627 39.991285 148)'::geometry, ST_SRID(point_lat_lon));


1

You can not use a function in the order by clause, because you are tempting to sort a table with a column that not exists. Try this CREATE TABLE line_attribute_point AS SELECT distinct on (ltest.id) ltest.*, ST_Distance(ltest.geom, ptest.geom) as distance, ST_ClosestPoint(ltest.geom, ptest.geom) as snapped_point FROM ...


1

Does that select do what you want? select ST_ClosestPoint(close_line.geom, ptest.geom) from ( select ltest.geom geom from ltest order by ST_Distance(ltest.geom, ptest.geom) limit 1 ) close_line, ptest


2

You have defined meterid as a PRIMARY KEY so they should be unique. You may use Composite Key CREATE TABLE MeterRdg( meterid VARCHAR(5) NOT NULL, reading integer, reader VARCHAR(15), DateRead date, PRIMARY KEY(meterid, DateRead) ) or add a column with counter and use it as a key for whole record CREATE TABLE MeterRdg( ...


0

There is not to my knowledge any ready-made, reusable solution for doing this. However, I can share what I've done in the past. There are three parts to what you're trying to accomplish: Automatically detecting and downloading updates to the GTFS feed. A simple approach might be to subscribe (in a feed-reader) to the RSS feed published by GTFS Data ...


1

You can try this via SQL Select: Select: * From Table(s): NODESTABLE Where: GROUNDLEVEL > (LIDARLEVEL + 0.5) In the example above replace: NODESTABLE with the name of your table GROUNDLEVEL with the name of the column holding the ground level LIDARLEVEL with the name of the column holding the LiDAR level Also note that the example above will give you ...


3

Thanks Alex for the answer; I also found that the reason my view was not updating was simply to me loading it with the cache table option checked. Un-checking this fixed it.


4

IF you have a view in PostGIS, you could use that as the table that you edit, so long as you only edit attributes from the master table. You could concatenate the action fields (from the one-to-many relationships) so that you have unique combination of actions to symbolize on. My point is, visualising from a view out of postgis will ensure that it's always ...


3

It seems you try to persist your entity and its id remains null. That's why your get a constraint violation error. To simply solve this error I suggest you to try create your database table using auto-incrementation option on your SHAPE field. It should solve your problem, I guess. OR Reading the documentation on LAST_INSERT_ID() I would suggest that the ...


4

In line $polywrite = "<script>document.write(polygon)</script>" you are trying to use javascript code while it is imposible in php side. All php scripts run before javascript. You can use proj4php as mentioned in the comments. Another solution is to fill the polywrite variable at javascript side and create an Ajax request sending `polywrite' to a ...


1

Thanks to @RhodiumToad on #postgres, trick is to use sub.geom instead of table. select row_to_json(t) from ( select * from ( select * from table where height>1500 ) as sub where st_intersects( st_transform( st_setsrid( ST_geomfromgeojson( ...



Top 50 recent answers are included