[postgis-tickets] r17621 - Support for srid reprojection when using -D switch
Regina Obe
lr at pcorp.us
Sun Jul 28 02:14:48 PDT 2019
Author: robe
Date: 2019-07-28 14:14:48 -0700 (Sun, 28 Jul 2019)
New Revision: 17621
Modified:
trunk/NEWS
trunk/doc/using_postgis_dataman.xml
trunk/loader/README.shp2pgsql
trunk/loader/shp2pgsql-cli.c
trunk/loader/shp2pgsql-core.c
trunk/loader/shp2pgsql-core.h
Log:
Support for srid reprojection when using -D switch
References #4403
Tests forthcoming
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/NEWS 2019-07-28 21:14:48 UTC (rev 17621)
@@ -16,6 +16,7 @@
- #4454, Speed up _ST_OrderingEquals (Raúl Marín)
- #4453, Speed up ST_IsEmpty (Raúl Marín)
- #4271, postgis_extensions_upgrade() also updates after pg_upgrade (Raúl Marín)
+ - #4403, Support for shp2pgsql ability to reproject with copy mode (-D) (Regina Obe)
PostGIS 3.0.0alpha3
2019/07/01
Modified: trunk/doc/using_postgis_dataman.xml
===================================================================
--- trunk/doc/using_postgis_dataman.xml 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/doc/using_postgis_dataman.xml 2019-07-28 21:14:48 UTC (rev 17621)
@@ -1672,7 +1672,6 @@
Optionally specifies that the input shapefile uses the given
FROM_SRID, in which case the geometries will be reprojected to the
target SRID.
- FROM_SRID cannot be specified with -D.
</para>
</listitem>
</varlistentry>
Modified: trunk/loader/README.shp2pgsql
===================================================================
--- trunk/loader/README.shp2pgsql 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/loader/README.shp2pgsql 2019-07-28 21:14:48 UTC (rev 17621)
@@ -53,10 +53,13 @@
the default "insert" SQL format. Use this for very large data
sets.
- -s <SRID>
- Creates and populates the geometry tables with the specified
- SRID.
+ -s [<FROM_SRID>:]<SRID>
+ Creates and populates the geometry tables with the specified SRID.
+ Optionally specifies that the input shapefile uses the given
+ FROM_SRID, in which case the geometries will be reprojected
+ to the target SRID.
+
-g <geometry_column>
Specify the name of the geometry column (mostly useful in append
mode).
@@ -113,8 +116,9 @@
AUTHORS
- Originally written by Jeff Lounsbury <jeffloun at refractions.net>.
- Improved and maintained by Sandro Santilli <strk at kbt.io>.
+ Originally written by Jeff Lounsbury <jeffloun at refractions.net>.
+ Improved and maintained by
+ Sandro Santilli <strk at kbt.io>, Regina Obe <lr at pcorp.us>.
Includes small contributions and improvements by others.
This application uses functionality from shapelib 1.2.9 by Frank
Modified: trunk/loader/shp2pgsql-cli.c
===================================================================
--- trunk/loader/shp2pgsql-cli.c 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/loader/shp2pgsql-cli.c 2019-07-28 21:14:48 UTC (rev 17621)
@@ -24,7 +24,7 @@
printf(_( "USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]\n"
"OPTIONS:\n" ));
printf(_( " -s [<from>:]<srid> Set the SRID field. Defaults to %d.\n"
- " Optionally reprojects from given SRID (cannot be used with -D).\n"),
+ " Optionally reprojects from given SRID.\n"),
SRID_UNKNOWN);
printf(_( " (-d|a|c|p) These are mutually exclusive options:\n"
" -d Drops the table, then recreates it and populates\n"
@@ -250,12 +250,6 @@
exit(1);
}
- if (config->dump_format && config->shp_sr_id != SRID_UNKNOWN)
- {
- fprintf(stderr, "Invalid argument combination - cannot use -D with -s FROM_SRID:TO_SRID\n");
- exit(1);
- }
-
/* Determine the shapefile name from the next argument, if no shape file, exit. */
if (pgis_optind < argc)
{
Modified: trunk/loader/shp2pgsql-core.c
===================================================================
--- trunk/loader/shp2pgsql-core.c 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/loader/shp2pgsql-core.c 2019-07-28 21:14:48 UTC (rev 17621)
@@ -786,6 +786,7 @@
state->widths = NULL;
state->precisions = NULL;
state->col_names = NULL;
+ state->col_names_no_paren = NULL;
state->field_names = NULL;
state->num_fields = 0;
state->pgfieldtypes = NULL;
@@ -1090,10 +1091,11 @@
state->precisions = malloc(state->num_fields * sizeof(int));
state->pgfieldtypes = malloc(state->num_fields * sizeof(char *));
state->col_names = malloc((state->num_fields + 2) * sizeof(char) * MAXFIELDNAMELEN);
+ state->col_names_no_paren = malloc((state->num_fields + 2) * sizeof(char) * MAXFIELDNAMELEN);
- /* Generate a string of comma separated column names of the form "(col1, col2 ... colN)" for the SQL
+ strcpy(state->col_names_no_paren, "" );
+ /* Generate a string of comma separated column names of the form "col1, col2 ... colN" for the SQL
insertion string */
- strcpy(state->col_names, "(" );
for (j = 0; j < state->num_fields; j++)
{
@@ -1243,24 +1245,28 @@
return SHPLOADERERR;
}
- strcat(state->col_names, "\"");
- strcat(state->col_names, name);
+ strcat(state->col_names_no_paren, "\"");
+ strcat(state->col_names_no_paren, name);
if (state->config->readshape == 1 || j < (state->num_fields - 1))
{
/* Don't include last comma if its the last field and no geometry field will follow */
- strcat(state->col_names, "\",");
+ strcat(state->col_names_no_paren, "\",");
}
else
{
- strcat(state->col_names, "\"");
+ strcat(state->col_names_no_paren, "\"");
}
}
/* Append the geometry column if required */
if (state->config->readshape == 1)
- strcat(state->col_names, state->geo_col);
+ strcat(state->col_names_no_paren, state->geo_col);
+ /** Create with (col1,col2,..) ( **/
+ strcpy(state->col_names, "(" );
+ strcat(state->col_names, state->col_names_no_paren);
+
strcat(state->col_names, ")");
@@ -1455,6 +1461,23 @@
}
}
+
+ /**If we are in dump mode and a transform was asked for need to create a temp table to store original data
+ You may ask, why don't we go straight into the main table and then do an alter table alter column afterwards
+ Main reason is so we don't incur the penalty of WAL logging when we change the typmod in final run. **/
+ if (state->config->dump_format && state->to_srid != state->from_srid){
+ /** create a temp table with same structure as main except for no restriction on geometry type */
+ stringbuffer_aprintf(sb, "CREATE TEMP TABLE \"pgis_tmp_%s\" AS SELECT * FROM ", state->config->table);
+ /* Schema is optional, include if present. */
+ if (state->config->schema)
+ {
+ stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
+ }
+ stringbuffer_aprintf(sb, "\"%s\" WHERE false;\n", state->config->table, state->geo_col);
+ /**out input data is going to be in different srid from target, so need to remove type constraint **/
+ stringbuffer_aprintf(sb, "ALTER TABLE \"pgis_tmp_%s\" ALTER COLUMN \"%s\" TYPE geometry USING ( (\"%s\"::geometry) ); \n", state->config->table, state->geo_col, state->geo_col);
+ }
+
/* Copy the string buffer into a new string, destroying the string buffer */
ret = (char *)malloc(strlen((char *)stringbuffer_getstring(sb)) + 1);
strcpy(ret, (char *)stringbuffer_getstring(sb));
@@ -1470,27 +1493,38 @@
int
ShpLoaderGetSQLCopyStatement(SHPLOADERSTATE *state, char **strheader)
{
- char *copystr;
+ //char *copystr;
+ stringbuffer_t *sb;
+ char *ret;
+ sb = stringbuffer_create();
+ stringbuffer_clear(sb);
+
/* Allocate the string for the COPY statement */
if (state->config->dump_format)
{
- if (state->config->schema)
- {
- copystr = malloc(strlen(state->config->schema) + strlen(state->config->table) +
- strlen(state->col_names) + 40);
+ stringbuffer_aprintf(sb, "COPY ");
- sprintf(copystr, "COPY \"%s\".\"%s\" %s FROM stdin;\n",
- state->config->schema, state->config->table, state->col_names);
+ if (state->to_srid != state->from_srid){
+ /** if we need to transform we copy into temp table instead of main table first */
+ stringbuffer_aprintf(sb, " \"pgis_tmp_%s\" %s FROM stdin;\n", state->config->table, state->col_names);
}
- else
- {
- copystr = malloc(strlen(state->config->table) + strlen(state->col_names) + 40);
+ else {
+ if (state->config->schema)
+ {
+ stringbuffer_aprintf(sb, " \"%s\".\" ", state->config->schema);
+ }
- sprintf(copystr, "COPY \"%s\" %s FROM stdin;\n", state->config->table, state->col_names);
+ stringbuffer_aprintf(sb, " \"%s\" %s FROM stdin;\n", state->config->table, state->col_names);
}
+
+
+ /* Copy the string buffer into a new string, destroying the string buffer */
+ ret = (char *)malloc(strlen((char *)stringbuffer_getstring(sb)) + 1);
+ strcpy(ret, (char *)stringbuffer_getstring(sb));
+ stringbuffer_destroy(sb);
- *strheader = copystr;
+ *strheader = ret;
return SHPLOADEROK;
}
else
@@ -1836,6 +1870,26 @@
sb = stringbuffer_create();
stringbuffer_clear(sb);
+
+ if ( state->config->dump_format && state->to_srid != state->from_srid){
+ /** We need to copy from the temp table to the real table, transforming to to_srid **/
+ stringbuffer_aprintf(sb, "ALTER TABLE \"pgis_tmp_%s\" ALTER COLUMN \"%s\" TYPE ", state->config->table, state->geo_col );
+ if (state->config->geography){
+ stringbuffer_aprintf(sb, "geography USING (ST_Transform(\"%s\", %d)::geography );\n", state->geo_col, state->to_srid);
+ }
+ else {
+ stringbuffer_aprintf(sb, "geometry USING (ST_Transform(\"%s\", %d)::geometry );\n", state->geo_col, state->to_srid);
+ }
+ stringbuffer_aprintf(sb, "INSERT INTO ");
+ // /* Schema is optional, include if present. */
+ if (state->config->schema)
+ {
+ stringbuffer_aprintf(sb, "\"%s\".", state->config->schema);
+ }
+ stringbuffer_aprintf(sb, "\"%s\" %s ", state->config->table, state->col_names);
+ stringbuffer_aprintf(sb, "SELECT %s FROM \"pgis_tmp_%s\";\n", state->col_names_no_paren, state->config->table );
+ }
+
/* Create gist index if specified and not in "prepare" mode */
if (state->config->readshape && state->config->createindex)
{
@@ -1913,6 +1967,9 @@
if (state->col_names)
free(state->col_names);
+ if (state->col_names_no_paren)
+ free(state->col_names_no_paren);
+
/* Free any column map fieldnames if specified */
colmap_clean(&state->column_map);
Modified: trunk/loader/shp2pgsql-core.h
===================================================================
--- trunk/loader/shp2pgsql-core.h 2019-07-22 13:27:46 UTC (rev 17620)
+++ trunk/loader/shp2pgsql-core.h 2019-07-28 21:14:48 UTC (rev 17621)
@@ -192,6 +192,9 @@
/* String containing colume name list in the form "(col1, col2, col3 ... , colN)" */
char *col_names;
+ /* String containing colume name list in the form "col1, col2, col3 ... , colN" */
+ char *col_names_no_paren;
+
/* String containing the PostGIS geometry type, e.g. POINT, POLYGON etc. */
char *pgtype;
More information about the postgis-tickets
mailing list