Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the develop
ment of a new workflow, to access data, analyze and map data stored in a dat
abase.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenan
ce of geo-spatial information, each vendor has chosen a fundamentally differ
ent approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the
underlying Oracle 9i kernel. Oracle allows users to interact directly with t
he database using SQL language. In addition, Oracle has published the struct
ure of its geometry, making it an open specification. Any GIS software is ca
pable accessing the data stored in Oracle. In addition, all business rules f
or data and geo-spatial data integrity is built into this model, and any acc
ess, input, edits or other interaction must adhere to the rules that are des
igned in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Eac
h GIS vendor is therefore responsible for developing a method for storing GI
S information in this database. Thus, Intergraph, ESRI, and MapInfo have dev
eloped their own binary geometry structures to allow them to store geo-spati
al information in SQL Server. This approach makes the geo-spatial informatio
n dependent on the GIS software that is used.
Can anyone please help me!docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the devel
opment of a new workflow, to access data, analyze and map data stored in a d
atabase.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and mainten
ance of geo-spatial information, each vendor has chosen a fundamentally diff
erent approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underl
ying Oracle 9i kernel. Oracle allows users to interact directly with the database us
ing SQL language. In addition, Oracle has published the structure of its geometry, m
aki
ng it an open specification. Any GIS software is capable accessing the data
stored in Oracle. In addition, all business rules for data and geo-spatial d
ata integrity is built into this model, and any access, input, edits or othe
r interaction must adhere t
o the rules that are designed in the database no matter the application accessing the databa
se.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS
vendor is therefore responsible for developing a method for storing GIS information
in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binar
y g
eometry structures to allow them to store geo-spatial information in SQL Server. This approa
ch makes the geo-spatial information dependent on the GIS software that is used.een">
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/0...arcsde_sql.html

Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, maki
ng it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere t
o the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary g
eometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06...rcsde_sql.html
sql

Geometry Data type in SQL Server 2005

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C64F35.46E93F30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the = development of a new workflow, to access data, analyze and map data = stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and = maintenance of geo-spatial information, each vendor has chosen a = fundamentally different approach to the implementation of this = capability.
Oracle has developed a geometry data type that is fully integrated with = the underlying Oracle 9i kernel. Oracle allows users to interact = directly with the database using SQL language. In addition, Oracle has = published the structure of its geometry, making it an open = specification. Any GIS software is capable accessing the data stored in = Oracle. In addition, all business rules for data and geo-spatial data = integrity is built into this model, and any access, input, edits or = other interaction must adhere to the rules that are designed in the = database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. = Each GIS vendor is therefore responsible for developing a method for = storing GIS information in this database. Thus, Intergraph, ESRI, and = MapInfo have developed their own binary geometry structures to allow = them to store geo-spatial information in SQL Server. This approach makes = the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
--=_NextPart_000_0008_01C64F35.46E93F30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Does SQL Server 2005 have a = geometry data type?

I am trying to figure out to use SQL = Server 2005 or Oracle
The primary requirement is to be able = to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.

The following is what I was able to = find on the web.
While = both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach = to the implementation of this capability.
Oracle = has developed a geometry data type that is fully integrated with the underlying Oracle = 9i kernel. Oracle allows users to interact directly with the database using = SQL language. In addition, Oracle has published the structure of its = geometry, making it an open specification. Any GIS software is capable accessing = the data stored in Oracle. In addition, all business rules for data and = geo-spatial data integrity is built into this model, and any access, input, edits or = other interaction must adhere to the rules that are designed in the database = no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is = therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own = binary geometry structures to allow them to store geo-spatial information in = SQL Server. This approach makes the geo-spatial information dependent on the = GIS software that is used.

Can anyone please help me!
--=_NextPart_000_0008_01C64F35.46E93F30--docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06_1qtr/arcsde_sql.html

Generating Multi Level nodes in Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.

<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below

Cheers :)
Alvin

SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @.ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

>From #Temp as TempTable INNER JOIN
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT(teohster@.gmail.com) writes:
> I am trying to produce XML like the first one I described and have yet
> to discover a way of associating the ChildRegister with the parent
> Device in XML. I am not sure if it is a limitation of SQL Server, or if
> my implementation is incorrect. If anyone could post hints or
> solutions, I would greatly appreciate it.
> A shortened version of the stored procedure is below

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.public.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating a PDF type report and embeding fonts

When a .pdf file is generated by reporting services, does it embed the fonts used into the .pdf?, or is there any way to tell reporting services to "embed all the fonts"? Thanks in advance.

Hi Sunny --

No, the PDF extension does not do font embedding...There is no way to force this behavior, either.

|||Thanks for the info dude!|||Is there a plan to add this to newer versions of Reporting Services?

Generating a PDF type report and embeding fonts

When a .pdf file is generated by reporting services, does it embed the fonts used into the .pdf?, or is there any way to tell reporting services to "embed all the fonts"? Thanks in advance.

Hi Sunny --

No, the PDF extension does not do font embedding...There is no way to force this behavior, either.

|||Thanks for the info dude!|||Is there a plan to add this to newer versions of Reporting Services?

Generate BCP data file with money columns using C#?

I need to use C# to generate the data files for BCP. Which C# data type I
should use to generate the data columns for SQL money money?
' money;
FileStream fs = new FileStream("test.txt", FileMode.CreateNew);
BinaryWriter bw = new BinaryWriter(fs);
....
bw.Write(money);
....
bw.Close();
fs.Close();nick (nick@.discussions.microsoft.com) writes:
> I need to use C# to generate the data files for BCP. Which C# data type I
> should use to generate the data columns for SQL money money?
> ' money;
> FileStream fs = new FileStream("test.txt", >
FileMode.CreateNew);
> BinaryWriter bw = new BinaryWriter(fs);
> .....
> bw.Write(money);
> .....
> bw.Close();
> fs.Close();
That depends on how you intend to run BCP. BCP can handle both text
formats and binary formats.
But assuming that you intend to use text file - that is after all the
most common - you should write the value as in -1212.2345. That is, you
should not use any currency character or thousands separator, and you should
force the decimal delimiter to be point, and not let regional settings
affect the output.
As for which data type you should use in C#, this may not be the best
newsgroup to ask in. Again, it depends on your exact needs, but you
probably should use an exact data type, that is not double.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||How about binary format? I think it may use less space and thus faster for
very big file? I know float works but it may not be good for money type...
"Erland Sommarskog" wrote:

> nick (nick@.discussions.microsoft.com) writes:
> FileMode.CreateNew);
> That depends on how you intend to run BCP. BCP can handle both text
> formats and binary formats.
> But assuming that you intend to use text file - that is after all the
> most common - you should write the value as in -1212.2345. That is, you
> should not use any currency character or thousands separator, and you shou
ld
> force the decimal delimiter to be point, and not let regional settings
> affect the output.
> As for which data type you should use in C#, this may not be the best
> newsgroup to ask in. Again, it depends on your exact needs, but you
> probably should use an exact data type, that is not double.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||nick (nick@.discussions.microsoft.com) writes:
> How about binary format? I think it may use less space and thus faster
> for very big file? I know float works but it may not be good for money
> type...
But binary format is more difficult to work with, as these are essentially
the native formats of SQL Server. Maybe money is a simple as a 64-bit
integer value with an implied decimal point. That's the type used in the
OLE DB interface, but whether that actually represents the actual storage
in SQL Server I don't know.
Of course, you compose an example table, and then bulk out that data in
native format and study it in a hex editor.
I strongly recommend that you start with a text file. If you really
need to trim performance later on, then you could look into to using
a binary format.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Generalisation schema Help(GIF Schema included)

I have a CASE(law firm type) table which uses a "case number" as a PK.
Each case can have zero-many "case plans" of three different types of
case plans, namely: (civil, tax, criminal).

I have created 3 different tables for each respective "case plan" type as their
details differ quite significantly. I have created an Audit table which holds
information about updates and inserts made to these tables.

My problem is referencing a specific "case plan" in the Audit table
as the CasePlanID in the Tax,Criminal,Civil tables could be the same, and
the only uniqueness to those tables is the table name (ie: Tax,Criminal,Civil).

I have thought of placing a "CONSTANT" column in each table with the type of the
Case plan as the value, but this introduces a lot of redundancy.
I have also thought of having an audit table for each respective case plan table
but this doesnt seem like good practice.

Perhaps a Generalisation class between Case and caseplans would help.

I also have to report across the case plans too.

Any help would be much appreciated ;-)What's interesting is that you have modelled them like they're inherited (as in OOP). What is the difference between the case plan types? Different columns? Are you likely to add another case plan type at some point in time?

Edit : forgive me i'm being a goon. You clearly state that their details differ quite significantly. What are those details? Can they be normalised?|||It would seem like your case plan type can be seperated into a seperate table as your case will ALWAYS have a plan type. So you can normalise those into another table:

CREATE TABLE CasePlanTypes(
PlanTypeID INT NOT NULL,
PlanName VARCHAR(255)
)|||There will only be 3 case plan types and no more.
The types differ quite a lot. there ARE about 10 columns of similarity between all 3, however, Civil and Criminal have about 20 additional columns and between civil and criminal, about 12 of those 20 are similar in data type, but semantically different. Example:
Tax: {Gross_amount_taxed_for_quarter, Net_Amount_taxed_for_Quarter}
Civil:{Gross_Amount_Reclaimed_for_quarter,Net_amou nt_reclaimed_for_quarter}