PostgreSQL/PostGIS + WWW SQL Designer + gvSIG (III)

Facebooktwittergoogle_plusredditpinterestlinkedinmail


[In English]
Para terminar con esta serie de posts, esta es mi experiencia con WWW SQL Designer:

  • WWW SQL Designer permite importar una BD de un servidor en el entorno de diseño, pero no permite exportar un diseño directamente sobre una BD en un servidor. Para importar tu diseño en una BD, la opción más sencilla es Save/Load -> Generate SQL (seleccionar el SQL apropiado para la BD que uses) -> Copiar el código (recomendable guardarlo en fichero) -> Abrir la BD y ejecutar el SQL.
  • Estoy usando lampp, así que eso influye en dónde están instalados php y WWW SQL Designer.
  • Instrucciones de instalación:

https://code.google.com/p/wwwsqldesigner/wiki/Installation

  • Modificaciones a las instrucciones de instalación:

create user “sqlExample” with password ‘mypasswd’;

grant all privileges on database “sqlDesignerExample” TO “sqlExample” with grant option;

  • Modificaciones en fichero index.php en /opt/lampp/htdocs/wwwsqldesigner-2.7/backend/php-postgresql/index.php

// Parameters for the application database

function setup_saveloadlist() {

Define(“HOST_ADDR”, “localhost”);            // if the database cluster is on the same server as this application use ‘localhost’ otherwise the appropriate address (192.168.0.2 for example).

Define(“PORT_NO”, “5432”);                    // default port is 5432. If you have or had more than one db cluster at the same time, consider ports 5433,… etc.

Define(“DATABASE_NAME”, “sqlDesignerExample”);     // leave as is

Define(“USER_NAME”, “sqlExample”);        // leave as is

Define(“PASSWORD”, “mypasswd”);                    // leave as is

Define(“TABLE”, “wwwsqldesigner”);            // leave as is

}

// Parameters for the database you want to import in the application

function setup_import() {

Define(“HOST_ADDR”, “localhost”);    // if the database cluster is on the same server as this application use ‘localhost’ otherwise the appropriate address (192.168.0.2 for example).

Define(“PORT_NO”, “5432”);            // default port is 5432. If you have or had more than one db cluster at the same time, consider ports 5433,… etc.

Define(“DATABASE_NAME”, “mybd”);    // the database you want to import

Define(“USER_NAME”, “postgres”);    // role having rights to read the database

Define(“PASSWORD”, “mypasswd”);        // password for role

  • Para activar php

Cambiar php.ini: sudo leafpad php.ini en /opt/lampp/etc (leafpad es el editor que yo uso, puede emplearse cualquier otro)

La extensión que hay que descomentar es extension=”pgsql.so” (esa extensión está en /opt/lampp/lib/php/extensions/no-debug-non-zts-20121212 y /usr/lib/php5/20121212)


[En español]
Let’s finisht this set of posts with WWW SQL Designer:

  • WWW SQL Designer allows to import a DB from a server into the design environment, but the other way round (exporting the design directly into the DB server) is not possible.  For importing your design into a DB, the easiest option is Save/Load -> Generate SQL (you should use the proper SQL for your DB) -> Copy that code (it is a good idea to keep it in a file) -> Open your DB and run that code.
  • I am using lampp, so the installation path of php and WWW SQL Designer is related to lampp.
  • Installation instructions:

https://code.google.com/p/wwwsqldesigner/wiki/Installation

  • Some modifications to these instructions:

create user “sqlExample” with password ‘mypasswd’;

grant all privileges on database “sqlDesignerExample” TO “sqlExample” with grant option;

  • Some modifications on file index.php at /opt/lampp/htdocs/wwwsqldesigner-2.7/backend/php-postgresql/index.php

// Parameters for the application database

function setup_saveloadlist() {

Define(“HOST_ADDR”, “localhost”);            // if the database cluster is on the same server as this application use ‘localhost’ otherwise the appropriate address (192.168.0.2 for example).

Define(“PORT_NO”, “5432”);                    // default port is 5432. If you have or had more than one db cluster at the same time, consider ports 5433,… etc.

Define(“DATABASE_NAME”, “sqlDesignerExample”);     // leave as is

Define(“USER_NAME”, “sqlExample”);        // leave as is

Define(“PASSWORD”, “mypasswd”);                    // leave as is

Define(“TABLE”, “wwwsqldesigner”);            // leave as is

}

// Parameters for the database you want to import in the application

function setup_import() {

Define(“HOST_ADDR”, “localhost”);    // if the database cluster is on the same server as this application use ‘localhost’ otherwise the appropriate address (192.168.0.2 for example).

Define(“PORT_NO”, “5432”);            // default port is 5432. If you have or had more than one db cluster at the same time, consider ports 5433,… etc.

Define(“DATABASE_NAME”, “mybd”);    // the database you want to import

Define(“USER_NAME”, “postgres”);    // role having rights to read the database

Define(“PASSWORD”, “mypasswd”);        // password for role

  • php activation:

Change php.ini: sudo leafpad php.ini en /opt/lampp/etc (leafpad is my text editor, of course you can use any other)

The extension to be uncommented is  extension=”pgsql.so” (it can be found at /opt/lampp/lib/php/extensions/no-debug-non-zts-20121212 and /usr/lib/php5/20121212)

PostgreSQL/PostGIS + WWW SQL Designer + gvSIG (II)

Facebooktwittergoogle_plusredditpinterestlinkedinmail


[In English]
Respecto a gvSIG 1.12.0, hay que tener en cuenta que:

  • Instalar gvSIG sin sudo y con jre propia (la que tengo por defecto es openjdk, y no queda claro que no de problemas)
  • Para lograr que abra los rasters hay que instalar lib64ncurses5-dev:i386 y lib64ncurses5:i386 desde Synaptics (mi Xubuntu es 64 bits)
    (averiguado gracias a: http://osgeo-org.1560.x6.nabble.com/Solucion-a-Formato-no-valido-al-cargar-un-archivo-raster-Debian-Wheezy-64bit-gvSIG-1-1-y-gvSIG-2-1-td5130483.html)
  • Otros enlaces que intentan resolver el problema de apertura de los rasters, aunque a mí no me han servido:
    Solution for gvSIG 1.12 problems in Windows 64bits operative systems
    http://comments.gmane.org/gmane.comp.gis.gvsig.user.es/22669
  • Ni gvSIG 2.0 ni anteriores soportan PostGIS 2.0; gvSIG 2.1 sí, y también QGIS, OpenJump y uDig. ¿Cómo solucionarlo para gvSIG 1.12.0?
    http://mappinggis.com/2012/11/como-conectar-gvsig-con-postgis-2-0/
  • Es recomendable, para evitar problemas en la conexión de gvSIG 1.12.0  a PostGIS, que las tablas y las columnas estén todas en minúsculas: a veces, si el campo mezcla mayúsculas y minúsculas, puede no reconocerse en gvSIG.


[En español]
Regarding gvSIG 1.12.0, you should take into account the following issues:

  • gvSIG should be installed without sudo and with a jre of its own (my default jre is openjdk, and some problems could arise).
  • For opening raster files, you should install lib64ncurses5-dev:i386 and lib64ncurses5:i386 from Synaptics (I use a 64 bits Xubuntu version)
    (I learned that thanks to [in Spanish] a: http://osgeo-org.1560.x6.nabble.com/Solucion-a-Formato-no-valido-al-cargar-un-archivo-raster-Debian-Wheezy-64bit-gvSIG-1-1-y-gvSIG-2-1-td5130483.html)
  • Some other links for solving that raster issue, though they did not work for me [in Spanish]:
    Solution for gvSIG 1.12 problems in Windows 64bits operative systems
    http://comments.gmane.org/gmane.comp.gis.gvsig.user.es/22669
  • Neither gvSIG 2.0 nor previous versions work with  Postgis 2.0; gvSIG 2.1 does, and also también QGIS, OpenJump and uDig.  How solve this question for gvSIG 1.12.0 [in Spanish]?
    http://mappinggis.com/2012/11/como-conectar-gvsig-con-postgis-2-0/
  • In order to avoid problems in the conection between gvSIG 1.12.0  and PostGIS,  tables and columns should be lower case:  in some cases, columns with both lower and upper case may be not recognized in gvSIG.

PostgreSQL/PostGIS + WWW SQL Designer + gvSIG (I)

Facebooktwittergoogle_plusredditpinterestlinkedinmail


[In English]
Estoy trabajando con PostgreSQL/PostGIS como base de datos, WWW SQL Designer para el diseño de la misma, y gvSIG 1.12.0 como GIS, todo ello bajo Xubuntu 14.04 (64 bits). La instalación y el uso de las tres herramientas tiene algunos trucos, que comentaré en varios posts. Hoy empezamos con PostgreSQL/PostGIS:

  •  La instalación de las dos herramientas se hace desde Synaptic
  • No instalo phppagadmin (necesita apache2, y yo voy a usar lamp)
  • Cosas que instalo:
  1. postgresql-server-dev-9.3
  2. postgresql-doc-9.3
  3. postgresql-client-9.3
  4. postgresql-client-common
  5. postgresql-9.3-postgis-2.1
  6. postgresql-9.3-postgis-2.1-scripts
  7. libpq-dv
  8. postgresql-common
  9. libpq5
  10. postgresql-doc
  11. postgresql-client
  12. libreoffice-base-drivers
  13. postgresql-contrib-9.3
  14. postgis
  15. postgresql-9.3
  16. postgresql
  17. liblwgeom-2.1.2
  • Para la configuración, resulta útil este link de ayuda.
  • Configuración básica del servidor. El usuario postgres está creado por defecto, pero bloqueado por seguridad para que no pueda accederse; por eso lo primero es cambiar la clave:
    sudo -u postgres psql postgres
       \password postgres ( e introducir nueva password cuando se pida)
       Ctrl+D
  • Crear la extensión adminpack (igual sólo es necesario para PostgreSQL 9.1, no estoy segura):
    sudo -u postgres psql
       CREATE EXTENSION adminpack;
  • Crear el servidor en pgAdmin (usuario postgres, clave la que se ha cambiado antes)
  • Añadir extensiones, una vez creada la BD:
    · Botón derecho del ratón -> New object -> New extension -> postgis primero, postgis_topology después.
    · Esto añade la tabla spatial_ref_sys; la antigua tabla geometry_columns es ahora una vista.


[En español]
I work with PostgreSQL/PostGIS (DB), WWW SQL Designer for designing the DB, and my GIS is gvSIG 1.12.0; the OS is Xubuntu 14.04 (64 bits). Installing and using these tools can be tricky, so I plan to write several posts about it. Today I will focus on PostgreSQL/PostGIS:

  • I have installed both tools from Synaptic.
  • I do not install phppagadmin (it requires apache2, but I will use lamp)
  • List of elements to install:
  1. postgresql-server-dev-9.3
  2. postgresql-doc-9.3
  3. postgresql-client-9.3
  4. postgresql-client-common
  5. postgresql-9.3-postgis-2.1
  6. postgresql-9.3-postgis-2.1-scripts
  7. libpq-dv
  8. postgresql-common
  9. libpq5
  10. postgresql-doc
  11. postgresql-client
  12. libreoffice-base-drivers
  13. postgresql-contrib-9.3
  14. postgis
  15. postgresql-9.3
  16. postgresql
  17. liblwgeom-2.1.2
    • This link is quite useful for configuration issues.
    • Basic server configuration: user postgres is created by default, but it is blocked for security reasons; therefore, the first thing to do is to change the password:
      sudo -u postgres psql postgres
      \password postgres (type new password when required)
      Ctrl+D
    • Now we create adminpack extension (maybe it should be done only in PostgreSQL 9.1, not sure about that):
      sudo -u postgres psql
      CREATE EXTENSION adminpack;
    • Create the server in pgAdmin (user postgres, password the one we have changed previously)
    • Once we have created the DB, we add extensions:
      · Right-click -> New object -> New extension -> first postgis primero, then postgis_topology.
      · This adds spatial_ref_sys table; the old geometry_columns table is now a view.