[Geoserver-users] Create View with MySQL datastore

GeoServer GeoServer User
1

Hi,

I have created a view in MySQL that simply does a join of two tables. The sql looks something like this:

create view profileszipgeom as select profiles.*, zips.the_geom from profiles, zips where profiles.zipcodeint != -1 and profiles.zipcodeint = zips.zipcodeint;

Now I am trying to add this view as a featureType in Geoserver. When I click the “generate” button in the FeatureType editor I receive the following error:

The FeatureType ‘profileszipgeom’ has a NULL extent.
HINT: the dataset is empty or has no default geometry attribute.

I do not think the dataset is empty and I am trying to use the the_geom field from the zips table as my geometry column. I know in the past when I tried to use a PostGIS View in Geoserver, I had to explicitly say which field to be used as the oid field. As describe in this article:

http://docs.codehaus.org/display/GEOSDOC/PostGIS+DataStore

Going back to my MySQL view problem, I have assigned a primary key to the view. This did not seem to solve my problem. I am not very familiar with what an oid is, but I believe MySQL does not have them. Therefore, I simply tried assigning my primary key field as a field simply named “oid” and this did not work either.

Has anyone run across this problem with the MySQL datastore? Or does anyone perhaps see what I may need to do based upon their experience with PostGIS views?

One last thing… If I simply create a view in MySQL from one table as a test, such as:

create view testzip as select * from zips;

This does work with geoserver and I am able to see all my points without a problem.

Thanks in advance,
Scott

Scott Pezanowski

email: scottpez@anonymised.com

2

Scott Pezanowski ha scritto:

Hi,
I have created a view in MySQL that simply does a join of two tables. The sql looks something like this:
create view profileszipgeom as select profiles.*, zips.the_geom from profiles, zips where profiles.zipcodeint != -1 and profiles.zipcodeint = zips.zipcodeint;

Now I am trying to add this view as a featureType in Geoserver. When I click the "generate" button in the FeatureType editor I receive the following error:
The FeatureType 'profileszipgeom' has a NULL extent.
HINT: the dataset is empty or has no default geometry attribute.

I am not familiar with MySQL, but trying to make a parallel with PostGIS, does it have a metadata table where you declare the geometry
columns, their SRS and so on?
In PostGIS in order to use view you have to create it and then register
the geometry columns in the view in the metadata table. May it be the
same with MySQL?

Cheers
Andrea