I have a PostgreSQL database made up of the OSM map data for London. I imported this data using osm2psql. I would like to:
The python code below seems to achieve this but with one problem. It only seems to be accessing as small section of the overall database.
import psycopg2
conn = psycopg2.connect("dbname=db user=username")
maincur = conn.cursor()
readcur = conn.cursor()
writecur = conn.cursor()
maincur.execute("DROP TABLE lines_red")
maincur.execute("CREATE TABLE lines_red (osm_id bigint, name text, way geometry, value float);")
maincur.execute("SELECT osm_id, ST_NPOINTS(way) FROM planet_osm_line")
for record in maincur:
pointlist = []
for i in range(0,record[1]):
readcur.execute("SELECT ST_ASTEXT(ST_POINTN(way, %s+1)) FROM planet_osm_line WHERE osm_id=%s;",(i,record[0]))
output = readcur.fetchone()
pointlist.append(output[0])
for i in range(0,record[1]-1):
if pointlist[i+1] != None:
value = 0.5
writecur.execute("INSERT INTO lines_red (name, way, value) VALUES ('testname', ST_Makeline(%s, %s), %s);", (pointlist[i],pointlist[i+1],value))
conn.commit()
maincur.close()
readcur.close()
writecur.close()
conn.close()
To illustrate the image below shows the full planet_osm_line table shown in grey and the result of the query shown in red. The red lines should cover the entire map as the code should traverse the full planet_osm_line table. I am using tilemill to display the results.
The problem in this case was with my use of TileMill.
When a new layer is created the default setting for its extent is to pre-calculate based on the database table used as input. This means that the layer will only ever be the size of the dataset with which it was initially created.
In this case I had iterated over a subset of my data as a test of my code and the extents were calculated based on the results. When I iterated over the whole data set the code iterated over the entire database but only displayed the results within the previously calculated constraints.
The solution is to create a new layer or set the extents setting in the layer to 'dynamic' rather than 'pre-calculate'