Per Henrik,
Great, I'm happy to hear you resolved the issue.
After playing with access to a SQL Server data store I'm amazed at how slow it is. I've been reading flat shapefiles from disk and GeoServer is very fast. But I have to say I'm very disappointed in the speed from a SQL Server. As an example reading from a shapefile the OpenLayers demo draws the layer in less than 2 seconds. From SQL it takes over 30 seconds. A feature request in from the shapefile in Openlayers also takes less than 2 seconds. From SQL it takes over 1 minute. The layer only has 887 polygons (school districts in Michigan, USA) and the attributes are nothing more than the name, area in miles and a few short code fields. Have you noticed slow responses as well from the method you use to access that data? I am running GeoServer 1.7.6 on a box with 4-3GB processors and 4 GB of memory. The SQL box is larger and has no use except by me. I did notice by running profiler that a feature request appears to be issuing this 3552 times in 3 sets for a total of 10656 commands:
SELECT "ID","FIPS_CODE","NAME","SQKM","SQMILES","ACRES","VER",CAST("geom".STSrid as VARCHAR) + ':' + "geom".STAsText() as "geom" FROM "MI_Schools"
Doesn't seem quite right as it's not passing in any predicate.
I also noticed that a simple pan or zoom of the map submitted over 3500 of these:
SELECT "ID","NAME",CAST("geom".STSrid as VARCHAR) + ':' + "geom".STAsText() as "geom" FROM "MI_Schools"
Regards,
Charles E. Deaton
Database Administrator
Realcomp II Ltd.
28555 Orchard Lake Road, Suite 200
Farmington Hills, MI 48334
General 248-553-3003 / Direct 248-699-9142 / Fax 248-699-0142
http://www.MoveInMichigan.com
-----Original Message-----
From: Per Henrik Johansen [mailto:phj@…2682…]
Sent: Wednesday, September 30, 2009 7:31 AM
To: Charles E. Deaton; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] sql 2008 could not find mapping for
geography column
Charles,
Solved the problem 
I had to use the GEOMETRY data type NOT Geography.
Regards,
Per Henrik
-----Original Message-----
From: Per Henrik Johansen [mailto:phj@…2682…]
Sent: 30. september 2009 12:05
To: Charles E. Deaton; geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] sql 2008 could not find mapping for
geography column
Charles,
I am new to GeoServer myself and don't have much experience with the
spatial capabilities in SQL Server - sounds like a bad combination
I have been able to add the SQL Server as a datastore and can view the
tables in the db. When I try to add a new feature type from a table, it
seems like GeoServer does not recognize my geography column. It can not
read the SRS, create bounding box, etc, and the column does not show up
in the table summary at the bottom of the FeatureType editor.
Since there is not much configuration to be done at the GeoServer side,
I'm suspecting I have missed some configuration issue(s) on SQL Server,
either at the server, database, table or column level.
Did you do anything special or did you manage to access SQL Server "out-
of-the-box"?
Regards,
Per Henrik
-----Original Message-----
From: Charles E. Deaton [mailto:CDeaton@…2551…]
Sent: 29. september 2009 15:15
To: Per Henrik Johansen; geoserver-users@lists.sourceforge.net
Subject: RE: [Geoserver-users] sql 2008 could not find mapping for
geography column
Per Henrik,
I'm somewhat new to GeoServer but was able to add a Datastore from a SQL
Server 2008 database and view it via the GeoServer demo/Openlayers page.
Are you able to view it there?
Regards,
Charles E. Deaton
Database Administrator
Realcomp II Ltd.
28555 Orchard Lake Road, Suite 200
Farmington Hills, MI 48334
General 248-553-3003 / Direct 248-699-9142 / Fax 248-699-0142
http://www.MoveInMichigan.com
> -----Original Message-----
> From: Per Henrik Johansen [mailto:phj@…2682…]
> Sent: Tuesday, September 29, 2009 6:27 AM
> To: Charles E. Deaton; geoserver-users@lists.sourceforge.net
> Subject: Re: [Geoserver-users] sql 2008 could not find mapping for
> geography column
>
> Charles,
>
> Yes, adding an index to the table took care of the "No primary key or
> unique index" warning. Thank you
>
> But I am still puzzled by "Could not find mapping" and quite unable to
> resolve it. It seems like GeoServer can not determine the type of the
> geography column. I guess I must do some tweeking on SQL Server, but
> don't know where or what?
>
> Regards,
>
> Per Henrik
>
> -----Original Message-----
> From: Charles E. Deaton [mailto:CDeaton@…2551…]
> Sent: 28. september 2009 15:40
> To: Per Henrik Johansen; geoserver-users@lists.sourceforge.net
> Subject: RE: [Geoserver-users] sql 2008 could not find mapping for
> geography column
>
> I am assuming that the index built when you created the IDENTITY on
the
> ID column was created as NONUNIQUE.
>
> > -----Original Message-----
> > From: Per Henrik Johansen [mailto:phj@…2680…]
> > Sent: Monday, September 28, 2009 8:58 AM
> > To: geoserver-users@lists.sourceforge.net
> > Subject: Re: [Geoserver-users] sql 2008 could not find mapping for
> > geography column
> >
> >
> > I have run into a similar problem.
> >
> > I am running SQL Server 2008 Express (10.0.1600). GeoServer is at
> > version
> > 1.7.6 with JDBC 1.2. Connection to the db, without specifying db
name,
> > works
> > like a charm.
> >
> > I designed a simple table in SQL Management Studio and added one
point
> > to
> > it. GeoServer bails out with the following warnings:
> >
> > No primary key or unique index found for phjtest.
> > Could not find mapping for 'theGeo', ignoring the column and setting
> the
> > feature type read only
> >
> > I am not able to figure out what is wrong with this setup, any help
> will
> > be
> > greatly appreciated.
> >
> > My table, and data, looks like this:
> >
> > CREATE TABLE [dbo].[phjtest](
> > [id] [bigint] IDENTITY(1,1) NOT NULL,
> > [theGeo] [geography] NULL,
> > [navn] [nvarchar](50) NULL
> > ) ON [PRIMARY]
> >
> > INSERT INTO phjtest (navn, theGeo)
> > VALUES(
> > 'Stiftsgården',
> > geography::STGeomFromText('POINT(10.393752 63.428874)',4326));
> >
> > Regards,
> >
> > Per Henrik
> >
> > MakaniKai wrote:
> > > For those struggling to connect to sql server 2008 as a datastore
I
> > got
> > > the
> > > unable to connect error until left out the database name.
> > >
> > > <snip>
> > --
> > View this message in context: http://www.nabble.com/sql-2008-could-
> not-
> > find-mapping-for-geography-column-tp22913505p25644798.html
> > Sent from the GeoServer - User mailing list archive at Nabble.com.
> >
> >
> > --------------------------------------------------------------------
--
> --
> > ------
> > Come build with us! The BlackBerry® Developer Conference in SF,
CA
> > is the only developer event you need to attend this year. Jumpstart
> your
> > developing skills, take BlackBerry mobile applications to market and
> > stay
> > ahead of the curve. Join us from November 9-12, 2009. Register
> > now!
> > http://p.sf.net/sfu/devconf
> > _______________________________________________
> > Geoserver-users mailing list
> > Geoserver-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/geoserver-users
> ----------------------------------------------------------------------
--
> ------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart
your
> developing skills, take BlackBerry mobile applications to market and
> stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
------------------------------------------------------------------------
------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and
stay
ahead of the curve. Join us from November 9-12, 2009. Register
now!
http://p.sf.net/sfu/devconf
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
------------------------------------------------------------------------
------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and
stay
ahead of the curve. Join us from November 9-12, 2009. Register
now!
http://p.sf.net/sfu/devconf
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
