[OSM-dev] Adding a table to osm2pgsql (and a couple utility patches) [patch]
I don't see an email address in the osm2pgsql source, so I'm just sending
this here; if there's a better destination, please let me know.
I think it would be really useful to have a table that tracks relation
membership so you can do JOINs to get the components of relations, so I
added one to osm2pgsql. If this is accepted into the trunk, I plan to use
it to submit some rendering patches to render highway shields from route
relations.
I'm attaching three patches; I've tried to keep my changes orthogonal,
plus I think the first two are improvements on the codebase even if no one
likes the planet_osm_member table:
* exportlist-indexing.patch changes exportList in output-pgsql.c so that
it's explicitly indexed by OsmType. Most of the code that uses it does
so with OsmType indexing, but a few spots treat it as if it's indexed
by table_id, which it isn't (at least, not any more).
* common-set-prefix.patch merely moves the set_prefix() function from
middle-pgsql.c into pgsql.h and uses it in output-pgsql.c.
* planet-osm-member.patch adds a new table, prefix_member, which
cross-references relations and their members. It's intended to allow
JOINs on the line, polygon, and node tables, so it has a field for the
member type, and all IDs referring to relations are negative.
The patch creates a distinction in output-pgsql.c between geometric
tables (ones with a way field) and non-geometric tables; the
distinction is made by the presence or absence of the type member of
the s_table struct.
--
...computer contrarian of the first order... / http://aperiodic.net/phil/
PGP: 026A27F2 print: D200 5BDB FC4B B24A 9248 9F7A 4322 2D22 026A 27F2
--- --
Hire an expert marksman to stand by the entrance to your fortress. His
job will be to shoot anyone who rides up to challenge you.
-- Evil Overlord's Handbook, entry 196
--- --
=== modified file 'osmtypes.h'
--- osmtypes.h 2010-11-07 18:54:12 +
+++ osmtypes.h 2011-01-13 13:49:52 +
@@ -6,6 +6,7 @@
#include keyvals.h
enum OsmType { OSMTYPE_WAY, OSMTYPE_NODE, OSMTYPE_RELATION };
+#define OSM_TYPE_COUNT 3
struct osmNode {
double lon;
=== modified file 'output-pgsql.c'
--- output-pgsql.c 2010-12-05 23:32:10 +
+++ output-pgsql.c 2011-01-13 13:49:52 +
@@ -86,8 +86,8 @@
int count;
};
-static struct taginfo *exportList[4]; /* Indexed by enum table_id */
-static int exportListCount[4];
+static struct taginfo *exportList[OSM_TYPE_COUNT]; /* Indexed by enum table_id */
+static int exportListCount[OSM_TYPE_COUNT];
/* Data to generate z-order column and road table
* The name of the roads table is misleading, this table
@@ -227,7 +227,7 @@
// Currently this scales with n^2 number of styles
int i,j;
-for (i=0; iNUM_TABLES; i++) {
+for (i=0; iOSM_TYPE_COUNT; i++) {
for(j=0; jexportListCount[i]; j++) {
if (exportList[i][j].name == name)
exportList[i][j].name = NULL;
@@ -240,14 +240,14 @@
static void free_style(void)
{
int i, j;
-for (i=0; iNUM_TABLES; i++) {
+for (i=0; iOSM_TYPE_COUNT; i++) {
for(j=0; jexportListCount[i]; j++) {
free(exportList[i][j].name);
free(exportList[i][j].type);
free_style_refs(exportList[i][j].name, exportList[i][j].type);
}
}
-for (i=0; iNUM_TABLES; i++)
+for (i=0; iOSM_TYPE_COUNT; i++)
free(exportList[i]);
}
=== modified file 'middle-pgsql.c'
--- middle-pgsql.c 2010-12-05 23:32:10 +
+++ middle-pgsql.c 2011-01-13 21:54:05 +
@@ -1085,26 +1085,6 @@
}
}
-/* Replace %s with prefix */
-static inline void set_prefix( const char *prefix, const char **string )
-{
- char buffer[1024];
- if( *string == NULL )
- return;
- sprintf( buffer, *string, prefix, prefix, prefix, prefix, prefix, prefix );
- *string = strdup( buffer );
-}
-
-/* Replace %s with prefix and tablespace*/
-static inline void set_prefixtbls( const char *prefix, const char *tbls, const char **string )
-{
- char buffer[1024];
- if( *string == NULL )
- return;
- sprintf( buffer, *string, prefix, prefix, tbls );
- *string = strdup( buffer );
-}
-
static int build_indexes;
static int pgsql_start(const struct output_options *options)
=== modified file 'output-pgsql.c'
--- output-pgsql.c 2011-01-13 13:49:52 +
+++ output-pgsql.c 2011-01-13 21:54:05 +
@@ -1141,12 +1141,8 @@
for (i=0; iNUM_TABLES; i++) {
PGconn *sql_conn;
-/* Substitute prefix into name of table */
-{
-char *temp = malloc( strlen(options-prefix) + strlen(tables[i].name) + 1 );
-sprintf( temp, tables[i].name, options-prefix );
-tables[i].name = temp;
-}
+set_prefix( options-prefix, (tables[i].name) );
+
fprintf(stderr, Setting up table: %s\n, tables[i].name);
sql_conn = PQconnectdb(options-conninfo);
=== modified file 'pgsql.h'
--- pgsql.h
Re: [OSM-dev] Can't login to https://trac.openstreetmap.org/login
bump .. who should I contact to fix it? thanks in advance On Mon, Jan 17, 2011 at 9:30 PM, Martin Ždila m.zd...@gmail.com wrote: I can't login to https://trac.openstreetmap.org/login with my username mar...@zdila.sk and correct password. These credentials works on the http://www.openstreetmap.org site. Could somebody help me with this? -- Ing. Martin Ždila tel:+421-908-363-848 mailto:mar...@zdila.sk http://www.zdila.sk/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Adding a table to osm2pgsql (and a couple utility patches) [patch]
* Phil! Gold phi...@pobox.com [2011-01-18 11:16 -0500]:
* planet-osm-member.patch
And I noticed after sending that this patch incorrectly uncomments the
DEBUG_PGSQL #define. Attached is the correct patch that only modifies
output-pgsql.c.
--
...computer contrarian of the first order... / http://aperiodic.net/phil/
PGP: 026A27F2 print: D200 5BDB FC4B B24A 9248 9F7A 4322 2D22 026A 27F2
--- --
Fireballs don't hurt me.
-- Famous Last Words, #719
--- --
=== modified file 'output-pgsql.c'
--- output-pgsql.c 2011-01-13 21:54:05 +
+++ output-pgsql.c 2011-01-18 15:56:10 +
@@ -38,7 +38,7 @@
#define MAX_STYLES 1000
enum table_id {
-t_point, t_line, t_poly, t_roads
+t_point, t_line, t_poly, t_roads, t_member
};
static const struct output_options *Options;
@@ -46,18 +46,27 @@
/* Tables to output */
static struct s_table {
//enum table_id table;
-char *name;
-const char *type;
+const char *name;
+const char *type; // Geometry type in the table. NULL for non-geometry tables.
PGconn *sql_conn;
char buffer[1024];
unsigned int buflen;
int copyMode;
char *columns;
+const char *create; // Command to create non-geometry tables.
+const char *index; // Command to create table indices.
+const char *copy; // Command to start COPY mode on non-geometry tables.
} tables [] = {
{ .name = %s_point, .type = POINT },
{ .name = %s_line,.type = LINESTRING},
{ .name = %s_polygon, .type = GEOMETRY }, // Actually POLGYON MULTIPOLYGON but no way to limit to just these two
-{ .name = %s_roads, .type = LINESTRING}
+{ .name = %s_roads, .type = LINESTRING},
+{ .name = %s_member, .type = NULL,
+ .columns = rel_id, member_id, mem_type, role,
+ .create = CREATE TABLE %s_member (rel_id int4, member_id int4, mem_type int4, role text);\n,
+ .index = CREATE INDEX %s_member_rel ON %s_member (rel_id);\n
+ CREATE INDEX %s_member_mem ON %s_member (member_id);\n,
+ .copy = COPY %s_member (rel_id, member_id, mem_type, role) FROM STDIN }
};
#define NUM_TABLES ((signed)(sizeof(tables) / sizeof(tables[0])))
@@ -267,7 +276,13 @@
/* Return to copy mode if we dropped out */
if( !tables[table].copyMode )
{
-pgsql_exec(sql_conn, PGRES_COPY_IN, COPY %s (%s,way) FROM STDIN, tables[table].name, tables[table].columns);
+ if (tables[table].type)
+ {
+ pgsql_exec(sql_conn, PGRES_COPY_IN, COPY %s (%s,way) FROM STDIN, tables[table].name, tables[table].columns);
+ } else
+ {
+ pgsql_exec(sql_conn, PGRES_COPY_IN, %s, tables[table].copy);
+ }
tables[table].copyMode = 1;
}
/* If the combination of old and new data is too big, flush old data */
@@ -1123,6 +1138,46 @@
return 0;
}
+static void pgsql_out_member(int id, struct member *members, int member_count)
+{
+static char *sql;
+static size_t sqllen=0;
+int slen;
+int i;
+
+if (sqllen==0) {
+ sqllen=2048;
+ sql=malloc(sqllen);
+}
+
+for (i=0; i member_count; i++) {
+ slen = strlen(members[i].role) + 1;
+if (slen sqllen) {
+sqllen = slen;
+sql = realloc(sql, sqllen);
+}
+ sprintf(sql, %d\t, -id);
+ copy_to_table(t_member, sql);
+
+ if (members[i].type == OSMTYPE_RELATION)
+ {
+ sprintf(sql, %d\t, -members[i].id);
+ } else
+ {
+ sprintf(sql, %d\t, members[i].id);
+ }
+ copy_to_table(t_member, sql);
+
+ sprintf(sql, %d\t, members[i].type);
+ copy_to_table(t_member, sql);
+
+ escape_type(sql, sqllen, members[i].role, text);
+ copy_to_table(t_member, sql);
+
+ copy_to_table(t_member, \n);
+}
+}
+
static int pgsql_out_start(const struct output_options *options)
{
char *sql, tmp[256];
@@ -1142,7 +1197,14 @@
PGconn *sql_conn;
set_prefix( options-prefix, (tables[i].name) );
-
+ if (!tables[i].type)
+ {
+ set_prefix( options-prefix, (tables[i].create) );
+ if (tables[i].index)
+ set_prefix( options-prefix, (tables[i].index) );
+ set_prefix( options-prefix, (tables[i].copy) );
+ }
+
fprintf(stderr, Setting up table: %s\n, tables[i].name);
sql_conn = PQconnectdb(options-conninfo);
@@ -1156,7 +1218,7 @@
if (!options-append) {
pgsql_exec(sql_conn, PGRES_COMMAND_OK, DROP TABLE IF EXISTS %s, tables[i].name);
}
-else
+else if (tables[i].type)
{
sprintf(sql, SELECT srid FROM geometry_columns WHERE f_table_name='%s';, tables[i].name);
res = PQexec(sql_conn, sql);
@@ -1179,111 +1241,128 @@
pgsql_exec(sql_conn, PGRES_COMMAND_OK, BEGIN);
-enum OsmType type = (i == t_point)?OSMTYPE_NODE:OSMTYPE_WAY;
-int numTags = exportListCount[type];
-struct taginfo *exportTags = exportList[type];
-if (!options-append) {
-sprintf(sql, CREATE TABLE %s ( osm_id int4, tables[i].name );
-for (j=0; j numTags; j++)
Re: [OSM-dev] Can't login to https://trac.openstreetmap.org/login
On 17/01/2011 20:30, Martin Ždila wrote: Hello I can't login to https://trac.openstreetmap.org/login with my username mar...@zdila.sk mailto:mar...@zdila.sk and correct password. These credentials works on the http://www.openstreetmap.org site. Have you tried logging in with your display name rather than your email address? Trac is working fine here, and I can log in. Also, there's no HTTPS support on trac, but you should get redirected if you try. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can't login to https://trac.openstreetmap.org/login
Hello On Tue, Jan 18, 2011 at 5:38 PM, Jonathan Bennett openstreet...@jonno.cix.co.uk wrote: On 17/01/2011 20:30, Martin Ždila wrote: Hello I can't login to https://trac.openstreetmap.org/login with my username mar...@zdila.sk mailto:mar...@zdila.sk and correct password. These credentials works on the http://www.openstreetmap.org site. Have you tried logging in with your display name rather than your email address? Trac is working fine here, and I can log in. Also, there's no HTTPS support on trac, but you should get redirected if you try. http://trac.openstreetmap.org/login redirects me to https protocol I also tried my screen name *Martin* - http://www.openstreetmap.org/user/*Martin*. This doesn't work either :-S. -- Ing. Martin Ždila tel:+421-908-363-848 mailto:mar...@zdila.sk http://www.zdila.sk/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Can't login to https://trac.openstreetmap.org/login
Hello MartinZ I had the same problem with trac.openstreetmap.org a couple of years ago. I have no idea who changed what, but eventually it just started working. If you have a secondary email address, register it with osm.org and try again ? Regards, Nic On Tue, Jan 18, 2011 at 7:17 PM, Martin Ždila m.zd...@gmail.com wrote: Hello On Tue, Jan 18, 2011 at 5:38 PM, Jonathan Bennett openstreet...@jonno.cix.co.uk wrote: On 17/01/2011 20:30, Martin Ždila wrote: Hello I can't login to https://trac.openstreetmap.org/login with my username mar...@zdila.sk mailto:mar...@zdila.sk and correct password. These credentials works on the http://www.openstreetmap.org site. Have you tried logging in with your display name rather than your email address? Trac is working fine here, and I can log in. Also, there's no HTTPS support on trac, but you should get redirected if you try. http://trac.openstreetmap.org/login redirects me to https protocol I also tried my screen name *Martin* - http://www.openstreetmap.org/user/*Martin*. This doesn't work either :-S. -- Ing. Martin Ždila tel:+421-908-363-848 mailto:mar...@zdila.sk http://www.zdila.sk/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Adding a table to osm2pgsql (and a couple utility patches) [patch]
Hi, Phil! Gold wrote: I think it would be really useful to have a table that tracks relation membership so you can do JOINs to get the components of relations, Would the planet_osm_rels table that is produced in --slim mode not have been suitable? Or asking this another way round, if I am using --slim mode can I now drop planet_osm_rels and use your table instead? Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Adding a table to osm2pgsql (and a couple utility patches) [patch]
* Frederik Ramm frede...@remote.org [2011-01-18 21:01 +0100]: Would the planet_osm_rels table that is produced in --slim mode not have been suitable? I tried using that table, but JOINs into the parts array were unacceptably slow, presumably because there was no index available for it. It's also not easy to use the planet_osm_rels table if you want to filter on roles or member types. Or asking this another way round, if I am using --slim mode can I now drop planet_osm_rels and use your table instead? Possibly, although I haven't familiarized myself with the middle-pgsql portion of osm2pgsql. My implementation doesn't store the relation's tags, for one thing; I plan on retrieving those from the planet_osm_line table for rendering purposes. When I get a chance (possibly not for a few days), I'll look at the middle layer and see if I can tell what it would take to use this table in both phases. -- ...computer contrarian of the first order... / http://aperiodic.net/phil/ PGP: 026A27F2 print: D200 5BDB FC4B B24A 9248 9F7A 4322 2D22 026A 27F2 --- -- I would be content if my children grew up to be the kind of people who think decorating consists mostly of building enough bookshelves. -- Anna Quindlen --- -- ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
